Microsoft KB Archive/109818

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

Article ID: 109818

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109818



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 InputBoxfunction 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.</ol>

Keywords: kbinfo kbprogramming KB109818

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.