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

ID: Q114556

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

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.

Create a new module with the following line in the Declarations section:

Option Explicit

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:

Open the Suppliers form in Design view.

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:

Open the Products And Suppliers form in Design view.

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.

REFERENCES

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

Keywords : kbusage FmsEvnt
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbhowto