Access Cookbook - Ken Getz [51]
varRetval = acbInputBox(Prompt:="Enter some text:", _
Title:="This is the title", Default:="Default Text", _
HelpFile:="msaccess.hlp", ContextID:=101)
Table 2-7. Parameters for acbInputBox
Argument
Optional?
Description
Prompt
No
String expression to be displayed as the prompt in the input box.
Title
Yes
String expression for the caption of the input box. If you omit this parameter, the caption will be empty.
Default
Yes
String expression displayed in the text box when the input box first pops up. If you omit this parameter, the text box will be empty.
XPos
Yes
Numeric expression that specifies, in twips, the distance between the left edge of the screen and the left edge of the input box. If you omit this parameter, the input box will be centered horizontally within the Access work area.
YPos
Yes
Numeric expression that specifies, in twips, the distance between the top edge of the screen and the top edge of the input box. If you omit this parameter, the input box will be centered vertically within the Access work area.
Helpfile
Yes
String expression that identifies the Help file to use to provide context-sensitive Help for the dialog. If Helpfile is provided, Context must also be provided.
Context
Yes
Numeric expression that is the Help context number the Help author assigned to the appropriate Help topic. If Context is provided, Helpfile must also be provided.
Once you've called the acbInputBox function, type a value into the text box on the form and press either the OK button (or the Return key) or the Cancel button (or the Escape key). Choosing OK returns the text you've typed, and choosing Cancel returns Null.
Discussion
This solution presents several useful techniques: how to use optional parameters, how to pop up a form and wait for a user response before returning a value back to the caller, how to initialize a pop-up form with values before presenting it to the user, and how to access online help programmatically.
Using optional parameters
Access allows you to declare and pass optional parameters to procedures that you create. That way, you can decide not to pass certain parameters and to use built-in defaults instead. For the acbInputBox function, only one parameter is required: the prompt. You can leave off all the rest, and the function will assign logical defaults for you. Here are a few comments on using optional parameters in your own procedures:
Once you use the Optional keyword in your procedure's declaration, all the subsequent parameters must also be optional.
Optional parameters can either be variants, or any specific data type.
If a Variant parameter is optional, use the IsMissing function in your code to determine whether the caller supplied a value for the parameter. If an optional parameter includes a specific type, specify the default value in the formal declaration of the method. See the VBA documentation for more information on this technique.
The code in acbInputBox either checks to see if the caller passed in a value for the optional parameters using the IsMissing function, or simply passes along the values supplied by the caller:
Public Function acbInputBox(Prompt As Variant, _
Optional Title As Variant, Optional Default As Variant, _
Optional XPos As Variant, Optional YPos As Variant, _
Optional HelpFile As Variant, Optional Context As Variant)
' This parameter is not optional.
varPrompt = Prompt
' Use a blank title if the caller didn't supply one.
varTitle = IIf(IsMissing(Title), " ", Title)
' Put text into the text box to start with.
varDefault = Default
' Specify the screen coordinates, in twips.
varXPos = XPos
varYPos = YPos
' Specify the help file and context ID.
varHelpFile = HelpFile
varContext = Context
' See the next section for the rest of the function.
Creating pop-up forms
You want to be able to call a function (acbInputBox) that will gather information and then pop up a form. That form will retain the focus until you are done with it, and then the function