Recipe 2.9 Create a Replacement for Access's InputBox

2.9.1 Problem

You'd like to be able to use Access's InputBox function in your applications, but it's so ugly! There doesn't appear to be any way to modify the way it looks, so you'd like to replace it with a standardized input form of your own. You'd also like to be able to call into your help file with a Help button on the input box.

2.9.2 Solution

The dialog you see when you run Access's InputBox function is just a form, like any other form, except that it's built into Access. You can create your own form, open it as a dialog form, and have it look any way you like. This solution demonstrates a technique you can use in many situations: creating a pop-up form that waits for input and, once it's done, allows the caller to retrieve the information gathered on the form. In this case, you'll call the acbInputBox function instead of InputBox, but the results will be the same.

Load and run frmTestInputBox from 02-09.MDB. This sample form gathers information and then calls the acbInputBox function to display the replacement input form. Once you're done with the input form, choose OK (to return the text you've entered) or Cancel (to discard it). The sample form will pop up a message box with the text you entered. Figure 2-16 shows the two forms at work.

Figure 2-16. Use frmTestInputBox to test the replacement input box

Follow these steps to include this functionality in your own applications:

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.

2.9.3 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.

2.9.3.1 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.

2.9.3.2 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 will return back to you the information it gathered on that form. The key to this process is in using acDialog as the WindowMode argument when opening the form. That way, the code processing in the original function waits, and the form doesn't relinquish the focus until you've either hidden it (which is what pressing the OK button does) or closed it (which is what pressing the Cancel button does). Once back in the original function, it can check to see if the form is still loaded (indicating that you pressed the OK button) and, if so, retrieve the information it needs directly from the form and then close the pop-up form. Here's the code from acbInputBox that does all that work:

    ' Open the form in dialog mode.  The code will
    ' stop processing, and wait for you to either close
    ' the form, or hide it.
    DoCmd.OpenForm acbcInputForm, WindowMode:=acDialog
    
    ' If you get here and the form is open, you pressed
    ' the OK button. That means you want to handle the
    ' text in the textbox, which you can get as the
    ' Response property of the form.
    If IsFormOpen(acbcInputForm) Then
        acbInputBox = Forms(acbcInputForm).Response
        DoCmd.Close acForm, acbcInputForm
    Else
        acbInputBox = Null
 End If

How do you know if the form is still open? This code uses the IsFormOpen function, as follows:

Private Function IsFormOpen(strName As String) As Boolean
   ' Is the requested form open?
   IsFormOpen = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function

IsFormOpen relies on the Access SysCmd function, which, among other things, can tell you the current state of any object. In this case, if there is any state for the object (that is, if SysCmd returns anything besides 0), the form must be open.

Finally, to retrieve the return value from the pop-up form, you can use a user-defined property of the form. In this case, we set up Response to be a property of the form that returns the value that you typed into the text box on the form. You could, of course, retrieve that value directly, but this means that the caller has to have information about the controls on the pop-up form. This way, by exposing a defined interface between the caller and the form, it doesn't matter how you rename or change controls on the form; as long as the form continues to provide the Response property, your code will still work.

To provide the read-only Response property, frmInputBox's module includes the following Property Get procedure:

Property Get Response( )
   ' Create a user-defined property, Response. This property 
   ' returns the value from the text box on the form.
   Response = Me.txtResponse
End Property

This procedure allows outsiders to retrieve what appear to be properties of the form itself. With this Property Get procedure in place, you can use syntax like this to retrieve the property:

acbInputBox = Forms(acbcInputForm).Response

VBA supports Property Let, Get, and Set procedures. See the VBA online help for more information.

2.9.3.3 Initializing pop-up forms

You've handled the input parameters and opened the dialog form. How do you tell that form what those parameters were? Just as forms can expose properties, modules can expose public variables that other modules and forms can view and modify. In this case, acbInputBox placed the appropriate parameters into various module public variables (varPrompt, varDefault, varXPos, etc.). Code attached to the pop-up form's Open event retrieves the values of those public variables and uses them to initialize itself. As shown in the following code, these variables can be accessed as properties of the module (basInputBox.varDefault, for example). Here is the Form_Open event procedure:

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo HandleErr
    
    Me.txtResponse = basInputBox.varDefault
    Me.Caption = basInputBox.varTitle
    Me.lblPrompt.Caption = basInputBox.varPrompt
    If Not IsNull(basInputBox.varHelpFile) And _
     Not IsNull(basInputBox.varContext) Then
        Me.cmdHelp.Visible = True
        ' Set things up for the Help button.
        mvarContext = basInputBox.varContext
        mvarHelpFile = basInputBox.varHelpFile
    Else
        Me.cmdHelp.Visible = False
    End If
    If Not IsNull(basInputBox.varXPos) Then
        DoCmd.MoveSize basInputBox.varXPos
    End If
    If Not IsNull(basInputBox.varYPos) Then
        DoCmd.MoveSize , basInputBox.varYPos
    End If

ExitHere:
    Exit Sub
    
HandleErr:
    ' Disregard errors.
    Resume Next
End Sub

2.9.3.4 Programmatically accessing online help

If you specify a help file and a context ID when you call acbInputBox, the code will enable a Help button on the form. When you click on that button, Access will load the help file, opened to the appropriate page. How did that all happen? The code attached to the Help button's Click event, shown here, calls the WinHelp API function, giving it a help file name, an action (acbcHELP_CONTEXT, indicating that the code wants to supply a context ID and have that page visible when the file opens), and the context ID you supplied. The following is the code that enables this functionality:

Const acbcHELP_CONTEXT = &H1&

Private Declare Function WinHelp _
 Lib "user32" Alias "WinHelpA" _
 (ByVal Hwnd As Long, ByVal lpHelpFile As String, _
 ByVal wCommand As Long, ByVal dwData As Any) As Long
Private Sub cmdHelp_Click( )
   WinHelp Me.hWnd, mvarHelpFile, acbcHELP_CONTEXT, CLng(mvarContext)
End Sub

Every page of a Windows help file can be accessed via the unique context ID that's assigned to it when you build the help file. Unfortunately, this is of use only if you've built the help file yourself or have a list of the context IDs for the various pages. No such list is available for the Access help file; even if it was, you cannot distribute the Access help file with your own applications. If you provide your own help file with your Access application, however, this technique makes it easy to have a help topic available at the click of a button.

2.9.3.5 Miscellaneous comments

The techniques presented here are not limited to this particular solution. You can use them any time you need to provide a modal dialog that gathers information and then returns that information once you're done with it. Once you've mastered the concepts in the "Creating pop-up forms" section, you will have a technique you can use over and over (for example, to provide a pop-up calendar form or a password input form).

The method we chose for initializing the pop-up form (using module public variables) is not the only method we could have used. Another popular method is to pass information to the form in its OpenArgs property: adding an OpenArgs parameter to the Open Form action allows you to pass information directly to the opening form. In this case, because there were many pieces of information to pass over (and the OpenArgs property is limited to a single string value), we would have had to write treacherous code to parse the string out to retrieve the values. Using the technique we chose, it's just a matter of reading the values from the module where they were declared. Though this may seem a little messy, it's a lot simpler in the long run.

2.9.4 See Also

To learn more about the IsMissing VBA function, search for "IsMissing" in the Access online help. See Recipe 7.6 in Chapter 7 to learn another technique for handling parameters. See Recipe 9.10 in Chapter 9 for another example of creating a reusable form. For more examples that call API functions, see Chapter 11.