ACC95: How to Create a FindRecord Form Method (139047)

SUMMARY

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

Microsoft Access version 7.0 now exposes the form module events, functions, and routines that have been created using Visual Basic for Applications. In earlier versions of Microsoft Access that use Access Basic, it was difficult to pass parameters to a form to allow searches, configuration, and so on. To do so usually involved setting the Tag property of the form or creating global variables to hold values.

The new form module functionality in Microsoft Access 7.0 enables you to code specific rules and behavior directly into the form object, and enables outside OLE applications or other functions within the database to use these functions to perform tasks from the very simple to the highly complex.

This article demonstrates a technique to enable a calling function to find a specific record on a form. This is done with a single call to a user-defined search function. The search function, defined in the form's code module, hides the complexity of the method used, which involves changing the focus, enabling and disabling a control, and using the FindRecord method.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications With Microsoft Access For Windows 95" manual.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.

MORE INFORMATION

To create the search function, follow these steps:

Open the sample database Northwind.mdb.
Open the Orders form in Design view.
Create a new module and type the following line in the Declarations section if it's not already there:
Option Explicit

Type the following procedure:

      Public Function FormFindRecord(IdValue As Variant) As Boolean
         On Local Error GoTo FormFindRecord_Err
         Me.SetFocus
         ' Set focus to OrderId control for lookup.
         Me!OrderID.Enabled = True
         DoCmd.GoToControl "OrderID"
         DoCmd.FindRecord IdValue, , True, , True
         DoEvents
         If Me!OrderID = IdValue Then
            FormFindRecord = True
         End If
      FormFindRecord_End:
         DoCmd.GoToControl "CustomerID"
         Me!OrderID.Enabled = False
         Exit Function
      FormFindRecord_Err:
         MsgBox Error$
         Resume FormFindRecord_End
      End Function

NOTE: The design of the Orders form disables the OrderID control so it cannot be edited. The above method is consistent with the form's design by not allowing the OrderID control to be edited. By encapsulating this rule in the function, you limit the visibility of this rule to the function itself and the caller of the function never needs to be concerned with this restriction.

Save and close the Orders form.
Open the Orders form in Form view.
Open the Debug window by pressing CTRL+G.
Type the following line in the Debug window, and then press ENTER.
? FORM_Orders.FormFindRecord(10400)
Note that the function runs and finds the record 10400.