ACC: Sample Function to Open a Form to a Particular Record (109818)

SUMMARY

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

This article shows you how to create a sample Visual Basic for Applications function that uses the InputBox()function to open a form to a particular record.

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 to the record where the CustomerID (or Customer ID in versions 1.x and 2.0) matches the value that you enter in the input box.

If you change "=" to ">=" on the OpenForm line in the function, the form opens to the record that 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.

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

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

Open the Customers form in Design view.
Add a command button to the form, and set the following property:
OnClick (or OnPush in version 1.x): =OpenFormWithInput()
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.