Tuesday, April 19, 2005

DAO Recordset Clone Find First String

If you need to find the first record in a recordset that matches a string value, you will need to use double quotes.
The procedure below is triggered by a command button called cmdFind_PONumber.
The name of the form is irrelevant, that is why we use " Me. " to designate the currently active form.
The form has a text box called txtFind where the search criteria is entered.

Example:

Private Sub cmdFind_PONumber_Click()

Dim strPONum As String
Dim db As DAO.Database
Dim rstClone As DAO.Recordset

Set db = CurrentDb
Set rstClone = Me.RecordsetClone
strPONum = Me.txtFind

' PONumber is the name of the field in the underlying table
' strPONum comes from the active form (Me.) and a text box called txtFind


rstClone.FindFirst "PONumber = """ & strPONum & """"
If rstClone.NoMatch Then
MsgBox "PO Number NOT Found!"
Else
MsgBox "PO Number Found!"
End If

End Sub

Monday, April 18, 2005

Add Conditions to your Access Macros

This simple one step macro will show a message box only when the value in a specified text box is null or empty.

Open a new macro or use an existing one.
Go to View / Conditions
A new column called "Condition" should have appeared on the left side of your screen.

For simplicity's sake, for the Action part of the macro, I have a MsgBox Action.

For the condition part, I am going to say:
[Forms]![Put your Form Name here].[Put your Text Box name here] Is Null

Saturday, April 16, 2005

Link Access to SQL Server

Instead of copying and pasting data from SQL Server into your Access tables; or instead of exporting tables from SQL Server to an Access database; or instead of importing data from SQL Server into Access; instead, why not link a table in Access to SQL Server?

Here’s how:
Step 01 – Create an ODBC connection for the SQL Server.
In Windows 2000, go to your control panel, in Administrative Tools go to Data Sources (ODBC).
Once the ODBC Data Source Administrator window opens, click on the System Tab.
Add / Select SQL Server (the very last one on the list)
On the next window, name the connection, describe it, and select the server.
Select next and configure how you will authenticate to the server.
Next, select the database you want to connect to.
Finish.
Test Data Source. If Test Successful, OK. If test not successful, go back and tweak your settings in the ODBC Data Source Administrator window.

Step 02 – Link the Access database to the SQL Server using the ODBC connection you just created.
Open Access, from the top menu, select Insert Tables, a pop up window appears, Select Link Table, on the next window, change the Files of Type to ODBC Databases ().
In the Select Data Source window select the Machine Data Source tab; select the name of the ODBC connection you created in Step 01, and finally select the table or view you want.

Trouble Shooting
If you do not see the table or view you are looking for, make sure that the username defined in SQL Server and which is being used by the ODBC connection has permissions to view the table.

If immediately after linking the table, Access wants to assign a unique identifier, you can select one, or simply ignore it by selecting OK.

Friday, April 15, 2005

Access Programmer

My new website is up, still a work in progress, check it out … Access Programmer

Access Programmer Website

Thursday, April 14, 2005

Access VBA DoCmd Object to open Form

The methods of the DoCmd object in Microsoft Access can be used to run actions from Visual Basic. Examples of said actions are: closing windows, opening forms and setting the value of a control.

For example to open a form call "Customers" in a normal view, as opposed to a design view or a table view, use this code:

DoCmd.OpenForm "Customers", acNormal


To open the same form but show only records for a customer called Acme Inc (the underlying table or query must have a field called [CompanyName]), use this code:

DoCmd.OpenForm "Customers", , ,"[CompanyName] = 'Acme Inc'"