ACC95: How to Create a FindRecord Form Method

• Microsoft Access version 7.0

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:

1. Open the sample database Northwind.mdb.

2. Open the Orders form in Design view.

3. Create a new module and type the following line in the Declarations section if it's not already there:

         Option Explicit
   

4. 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.
   
   

5. Save and close the Orders form.

6. Open the Orders form in Form view.

7. Open the Debug window by pressing CTRL+G.

8. 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.

Keywords          : JetFind kbprg kbusage PgmHowTo FmsHowTo
Version           : 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto