ACC: Sample Function to Open a Form to a Particular Record

MORE INFORMATION

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type the following procedure:
   
   In Microsoft Access 7.0 and 97:

         Function OpenFormWithInput()
         Dim Msg, Title, Defvalue, Answer
         Msg = "Enter a Customer ID (AROUT, CACTU, THEBI, etc.)."
         Title = "OPEN CUSTOMERS FORM"
         Defvalue = "CACTU"
         Answer = InputBox(Msg, Title, Defvalue)
         If Answer <> "" Then
            DoCmd.OpenForm "Customers", , , "[CustomerID]='" & Answer & "' "
         Else
            DoCmd.OpenForm "Customers"
         End If
         End Function
   						

   In Microsoft Access 1.x and 2.0:

         Function OpenFormWithInput()
         Dim Msg, Title, Defvalue, Answer
         Msg = "Enter a Customer ID (AROUT, CACTU, THEBI, etc.)."
         Title = "OPEN CUSTOMERS FORM"
         Defvalue = "BOTTM"
         Answer = InputBox(Msg, Title, Defvalue)
         If Answer <> "" Then
            DoCmd OpenForm "Customers", , , "[Customer ID]='" & Answer & "' "
         Else
            DoCmd OpenForm "Customers"
         End If
         End Function
   						

4. Open the Customers form in Design view.
5. Add a command button to the form, and set the following property:

   OnClick (or OnPush in version 1.x): =OpenFormWithInput()

6. Open the form in Form view and click the command button. Accept the default value in the input box (or type any valid CustomerID) and click OK. Note that the form opens to the designated customer.
   
   NOTE: If you type an invalid CustomerID in the input box, Microsoft Access displays a blank form.