This article describes a method that you can use to find a record in a form using the form's recordset and a bookmark.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

Back to the top

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] & "#"
				

Back to the top

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.

Back to the top

Microsoft Access "User's Guide," version 1.0, Chapter 22, "Using Macros with Forms," pages 547-548

Microsoft Access "User's Guide," version 1.1, Chapter 22, "Using Macros with Forms," pages 551-552

Microsoft Access "User's Guide," version 2.0, Chapter 25, "Using Macros with Forms," pages 651-652

Back to the top