ACC: How to Find a Record in a Form Using Access Basic (114556)

MORE INFORMATION

The Microsoft Access "User's Guide" demonstrates a method using the FindRecord macro action that you can use to find records in a form by selecting a key value from a list. (See the Microsoft Access "User's Guide," version 1.0, pages 547-548, or version 1.1, pages 551-552, or version 2.0, pages 651-652.) The method described in the User's Guide is limited in the following ways:

• It requires that the key search field be located on the form.
• You are limited to searching for a single key value.

The example below demonstrates a sample user-defined Access Basic function called FindRecord_RS() that uses the FindFirst recordset method to find a record in a form:

NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscores from the end of the line when re-creating this example.

1. Create a new module with the following line in the Declarations section:
   
   Option Explicit
2. Add the following function to the module:
   

         Function FindRecord_RS (SQLWhere)
            Dim DS As Dynaset
            Set DS = Screen.ActiveForm.Dynaset
            DS.FindFirst SQLWhere
            If DS.NoMatch Then
               MsgBox "No record found!"
            Else
               Screen.ActiveForm.Bookmark = DS.Bookmark
            End If
         End Function
   						

The FindRecord_RS() function's SQLWhere argument must be a string expression that is any valid SQL WHERE clause (without the word WHERE).

The following are examples of valid SQL WHERE clauses:

   "[Customer ID] = 3"

   "[Customer ID] = " & [Find Customer]
				

If the field is a text field, the WHERE clause may look like the following examples:

   "[Customer ID] = '3'"

   "[Customer ID] = '" & [Find Customer] & "'"
				

NOTE: You can use single or double quotation marks to delimit text values. The following examples are functionally identical to the two examples above:

   "[Customer ID] = ""3"""

   "[Customer ID] = """ & [Find Customer] & """"
				

The SQL WHERE clause to find a record for a particular customer on a given date might look like the following examples:

   "[Customer ID] = 3 AND [Order Date] = #1/1/94#"

   "[Customer ID] = " & [Find Customer] & " AND [Order Date] = #" &_

    [Find Order Date] & "#"
				

Using the FindRecord_RS Function

Microsoft Access version 1.x:

The Suppliers form in the sample database NWIND.MDB has a combo box called Select Company To Find that lists available suppliers. When you select a supplier, the Find Company macro is run to find the supplier matching the selection using the FindRecord action.

The following steps demonstrate how to use the FindRecord_RS() method instead of the Find Company macro:

1. Open the Suppliers form in Design view.
2. Select the Select Company To Find combo box in the form footer, and change the AfterUpdate property from
   

         Find Company
   						

   
   to:
   

         =FindRecord_RS("[Company Name] = """ & [Company Pick List] & """")
   						

When you make a selection in the combo box, the FindRecord_RS() function will find a record in the Suppliers form whose Company Name field matches your selection in the combo box.

Microsoft Access version 2.0:

The Products And Suppliers form in the sample database NWIND.MDB has a combo box called Select Product To Find that lists the available products. When you select a product, the Product Pick List.Find Product macro is run to find the product record matching the selection using the FindRecord macro action.

The following example demonstrates how to use the FindRecord_RS() method instead of the Product Pick List.Find Product macro:

1. Open the Products And Suppliers form in Design view.
2. Select the Select Product To Find combo box in the form header, and change the AfterUpdate property from
   

         Product Pick List.Find Product
   						

   
   to:
   

         =FindRecord_RS("[Product Name] = """ & [Product Pick List] & """")
   						

   
   

When you select an item in the combo box, the FindRecord_RS() function will find a record in the Products And Suppliers form whose Product Name field matches your selection in the Select Product To Find combo box.