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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes a sample Visual Basic for Applications function you can use to open a form to a particular record using the InputBox() function.

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 your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

The OpenFormWithInput() function opens the form with the record where the CustomerID (or Customer ID in versions 1.x and 2.0) matches the value you enter in the input box.

If you change "=" to ">=" on the OpenForm line in the function, the form opens to the record you want and displays subsequent records as well. For example, if you type CACTU as the CustomerID in the input box, CustomerID AROUT is not part of the form's record set, but THEBI is.

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:

         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.

Keywords          : kbprg PgmHowTo FmsHowTo
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo