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.
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.
Follow these steps to include this functionality in your own applications:
Import frmInputBox from 02-09.MDB
into your
database. Modify its appearance any way you like: change its size,
colors, fonts, or any other layout properties. Because the form
includes a module that handles its setup, you’ll want to use
the form we’ve supplied rather than creating your own.
Import the module basInputBox from 02-09.MDB
. If
you modified the form’s name in Step 1, you’ll need to
modify the code in basInputBox, making the
acbcInputForm
constant match the actual name of
the form.
To use the new input box, call the acbInputBox function that’s in basInputBox. It requires one parameter and accepts a number of optional parameters, as shown in Table 2-7. These parameters exactly match the parameters used by Access’s own InputBox function. The general syntax for acbInputBox is:
varRetval = acbInputBox(Prompt
[,Title
][,Default
][,Xpos
][,Ypos
] _ [,Helpfile
,Context
])
Table 2-7. Parameters for acbInputBox
Argument |
Optional? |
Description |
---|---|---|
|
No |
String expression to be displayed as the prompt in the input box. |
|
Yes |
String expression for the caption of the input box. If you omit this parameter, the caption will be empty. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
For example, to match the function call in Figure 2-16, you could use code like this:
varRetval = acbInputBox(Prompt:="Enter some text:", _ Title:="This is the title", Default:="Default Text", _ HelpFile:="msaccess.hlp", ContextID:=101)
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
.
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.
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:
The code in acbInputBox checks each parameter to see if the caller supplied a value. If not, it uses its own built-in default values, as shown in the following code:
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 = IIf(IsMissing(Default), Null, Default) ' Specify the screen coordinates, in twips. varXPos = IIf(IsMissing(XPos), Null, XPos) varYPos = IIf(IsMissing(YPos), Null, YPos) ' Specify the help file and context ID. varHelpFile = IIf(IsMissing(HelpFile), Null, HelpFile) varContext = IIf(IsMissing(Context), Null, Context) ' Open the form in dialog mode. The code will stop processing ' and wait for you to either close the form or hide it. ' See the next section for the rest of the function.
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 acbcInputFormacbcInputForm, 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 text box, 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 know 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 it 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, Reponse. 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
Access supports Property Let, Get, and Set procedures. See the Access online help for more information.
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 up things 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: ' No error can occur here, I don't think, that ' would make the form open invalid. Resume Next End Sub
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( ) ' Really, you don't care if this call fails! 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.
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.
Although it is documented as
supporting the Helpfile
and
ContextID
parameters, the built-in Access
InputBox function does not actually support
them. The help file includes those parameters for compatibility with
Visual Basic, but they don’t work from Access. This replacement
for the InputBox function includes the
parameters and implements their functionality.