ID: Q139047
The information in this article applies to:
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.
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
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.
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 : kbprg kbusage FmsHowto
Version : 7.0
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: August 29, 1997