Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

3. 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])

   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)

 Prompt

 Title

 Default

 XPos

 YPos

 Helpfile

 Context

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

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.