Extending the InputBox function for MS Access Forms

Access provides a rich functionality in terms of controls
that are available for use on forms, and what you can do with each control. For
instance, the Text Box control has an InputMask property that lets you control
how whatever the user types into the text box appears. Depending on what value
you choose for the InputMask property, you can have the user’s input look like
a telephone number without requiring the user to type parentheses or dashes, or
you can have the input converted to asterisks in the text box, so that no one
can read the password over his/her shoulder.

Unfortunately, this ability does not extend to the text box
presented when you use the InputBox function in VBA to capture ad hoc input
from a user.

While it’s not possible to change the behavior of the
InputBox function, I’m going to show you how to create your own form that does
use the Password Input Mask and use that form instead of the one generated by
the VBA InputBox function. In fact, I’m going to show you two different ways to
do this, so that you can choose whichever one you prefer.

Getting Started

In actual fact, you can do what I’m about to demonstrate with
any form. However, for the purposes of this article, I created a form (frmInputBox_Password,
shown in Figure 1) that closely resembles what’s produced by using the InputBox
function.

frmInputBox_Password in Design view

Figure 1: frmInputBox_Password in Design view. This form will be used to replicate the
appearance of the InputBox function’s form

It’s important to recognize that the InputBox function produces
a Dialog Box. Dialog Boxes have the ability to stop all other processing from
running until the Dialog Box has been dismissed. When you open a Dialog Box, you’re
stuck in that dialog. You can’t use the menu bar, you can’t right click, in
fact you can’t do anything else. All other processing stops until the Dialog
Box has been shut down. This contrasts with Modal forms, which really only
insist that you close the form before you can move the focus to another object,
but all other code continues to function.

Why a Dialog Box is useful is because often you have code
that you want to stop from running until the user has provided the request
information. If I were strictly to open the form that I presented in Figure 1
in the middle of a routine in my code, execution of the rest of my form’s code
would not stop to wait until the user provided the information. And while it is
possible to open a form as a Dialog Box by using a WindowMode of acDialog when
using the OpenForm method, the fact that all other code stops running makes it
difficult for the calling routine to retrieve the values entered onto the
second form.

Now, one way around this is to recognize that you can set the
Visible property for a form opened in Dialog mode to False (i.e. make the form
invisible), and the form is no longer a Dialog Box. The first approach I’m
going to demonstrate takes advantages of that fact.

Creating your own InputBox function

To replace the VBA InputBox function, I need a function that’s
capable of launching my form and sitting there, waiting until the user clicks
on either the OK or Cancel buttons. If the user clicks on the Cancel button, I
really don’t care whether or not anything was typed into the text box: I don’t
want a value returned by the function. However, if the user clicks on the OK
button, I want to know what was typed. Okay, so how do I do this?

Since, as I mentioned above, it’s possible to open a form as a Dialog Box by using the acDialog property for its WindowMode in the OpenForm method, having all processing stop and wait until the user does something isn’t difficult. How, though, can I have different behaviors depending on which button is clicked?

Remember that all logic in the function stops executing when
a Dialog Box is opened, and there are only two ways to have the processing
resume: either the Dialog Box is closed, or it’s made invisible. If I have the
Cancel button close my form, but have the OK Button only make it invisible, I
can put logic immediately after the OpenForm command that checks whether or not
the form I opened still exists, and thus know which button was clicked.

It literally is as simple as having the logic shown in
Listing 1 in the form:

Private Sub cmdCancel_Click()

  DoCmd.Close acForm, Me.Name

End Sub

Private Sub cmdOK_Click()

  Me.Visible = False

End Sub


Listing 1: VBA in frmInputBox_Password. If the user
clicks on the Cancel button, the form is closed. If the user clicks on the OK
button, the form is made invisible.

and the logic shown in Listing 2 in the function that calls
the form:

  DoCmd.OpenForm FormName:="frmInputBox_Password", _
    WindowMode:=acDialog

  If CurrentProject.AllForms("frmInputBox_Password").IsLoaded Then
' The OK button was clicked. Retrieve the value from the input box.
  Else
' The Cancel button was clicked. There is no value to retrieve.
  End If


Listing 2: An extract from PasswordInputBox, the function
which uses frmInputBox_Password to replace the InputBox function. When control
is returned to the function (either by the form being closed or being made
invisible), we check whether the form is still open, and act accordingly.

Pretty easy, isn’t it? To replicate the functionality of the
InputBox function, we need to be able to pass some information to the form
being opened, but the OpenArgs parameter of the OpenForm method gives us a
means to do this.

Specifically, the syntax for the InputBox function is:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

where those named arguments are defined as:

Part

Description

prompt

Required. String expression displayed as the message in
the dialog box. The maximum length of prompt is approximately 1024
characters, depending on the width of the characters used. If prompt consists
of more than one line, you can separate the lines using a carriage return
character (Chr(13)), a linefeed character (Chr(10)), or carriage
return–linefeed character combination (Chr(13) & Chr(10)) between each
line.

title

Optional. String expression displayed in the title bar of
the dialog box. If you omit title, the application name is placed in the
title bar.

default

Optional. String expression displayed in the text box as
the default response if no other input is provided. If you omit default, the
text box is displayed empty.

xpos

Optional. Numeric expression that specifies, in twips, the
horizontal distance of the left edge of the dialog box from the left edge of
the screen. If xpos is omitted, the dialog box is horizontally centered.

ypos

Optional. Numeric expression that specifies, in twips, the
vertical distance of the upper edge of the dialog box from the top of the
screen. If ypos is omitted, the dialog box is vertically positioned
approximately one-third of the way down the screen.

helpfile

Optional. String expression that identifies the Help file
to use to provide context-sensitive Help for the dialog box. If helpfile is
provided, context must also be provided.

context

Optional. Numeric expression that is the Help context
number assigned to the appropriate Help topic by the Help author. If context
is provided, helpfile must also be provided.

MSDN

Now, to keep this article to a reasonable length, I’m going
to ignore all but the first three parameters in that list of arguments. Even with
this simplification, though, remember that the OpenArgs is only a single
parameter that can be passed to the form being opened. How can we pass up to
three separate values using a single parameter?

The approach I generally use is to concatenate the arguments
being passed into a delimited string. You may have other approaches, but my favorite
is to have each individual argument as a pair in the form of ArgumentName=ArgumentValue,
and then have each of those pairs concatenated with a character that I live
without using in either ArgumentName or ArgumentValue. In this case, I decided
to use the tilde character (~): you may choose a different character if you
like.

The entirety of my PasswordInputBox function is shown in
Listing 3,

Function PasswordInputBox( _
  Prompt As String, _
  Optional Title As String = vbNullString, _
  Optional Default As String = vbNullString _
) As String

Dim strOpenArgs As String

  strOpenArgs = "Prompt=" & Prompt
  If Len(Title) > 0 Then
    strOpenArgs = strOpenArgs & "~Title=" & Title
  End If
  If Len(Default) > 0 Then
    strOpenArgs = strOpenArgs & "~Default=" & Default
  End If

  DoCmd.OpenForm FormName:= "frmInputBox_Password", _
    View:=acNormal, _
    WindowMode:=acDialog, _
    OpenArgs:=strOpenArgs

  If CurrentProject.AllForms("frmInputBox_Password").IsLoaded Then
    PasswordInputBox = Forms("frmInputBox_Password")!txtInput
    DoCmd.Close acForm, "frmInputBox_Password"
  Else
    PasswordInputBox = vbNullString
  End If

End Function


Listing 3: The complete VBA for PasswordInputBox. I
chose to return a zero-length string ("") if the Cancel button is
invoked on frmInputBox_Password, but you can chose a different action.

Look at how I build up the content of the variable strOpenArgs.
Let’s assume I passed the string What is your password? as the value for
Prompt, Enter Password as the value for Title, and nothing as the value
for Default. strOpenArgs will end up looking like what’s shown in Listing 4.

Prompt=What is your password?~Title=Enter Password


Listing 4: Sample content for OpenArgs, an argument
passed when opening frmInputBox_Password. Note the tilde (~) between the
question mark and the word Title.

Listing 5 shows the code for the Load event of my form. That
code parses what was passed in the OpenArgs string, and initializes values
appropriately:

Private Sub Form_Load()

Dim lngLoop As Long
Dim strTitle As String
Dim strDefault As String
Dim varOpenArgs As Variant
Dim varOpenArg As Variant

  If Len(Me.OpenArgs & vbNullString) > 0 Then
    varOpenArgs = Split(Me.OpenArgs, "~")
    For lngLoop = LBound(varOpenArgs) To UBound(varOpenArgs)
      varOpenArg = Split(varOpenArgs(lngLoop), "=")
      If UBound(varOpenArg) = 1 Then
        Select Case varOpenArg(0)
          Case "Prompt"
            Me!lblPrompt.Caption = varOpenArg(1)
          Case "Title"
            strTitle = varOpenArg(1)
          Case "Default"
            strDefault = varOpenArg(1)
        End Select
    Next lngLoop
  End If

  If Len(strTitle) = 0 Then
    strTitle = Application.Name
  End If

  Me.Caption = strTitle
  Me!txtInput = strDefault
  Me!txtInput.SetFocus

End Sub


Listing 5: The Load event for frmInputBox_Password,
showing how the OpenArgs argument is used. Usually I include some rudimentary
error checking, to insure that what’s passed is appropriate, but it’s been
removed for this sample.

Assuming that something was passed as the OpenArgs parameter,
I use the Split function to deconstruct that into the various argument pairs.
For the sample value of OpenArgs shown in Listing 4, the Split function will assign the string Prompt=What is your password? to array element varOpenArgs(0), and the string Title=Enter Password to varOpenArgs(1).
After that split, the code loops
through each element in varOpenArgs, using the Split function to split the
pairs of information at the equal signs. The first time through the loop (with varOpenArgs(0)
equal to Prompt=What is your password?), varOpenArg(0) gets set to Prompt,
and varOpenArg(1) is What is your password? That means that the Select
Case statement sets the Caption property of label lblPrompt to What is your
password?
The second time through the loop (where varOpenArgs(1) to the
string Title=Enter Password), varOpenArg(0) is set to Title, and varOpenArg(1)
is Enter Password, so the Select Case statements sets the variable strTitle
to Enter Password. If, after the loop is finished, strTitle hasn’t had a
value set, it’s set to Application.Name (which is the name of the currently
running application, or Microsoft Access. The form’s Caption property
gets set to the content of strTitle, the input text box gets set to the content
of strDefault, and focus is set to the input text box.

Apologies for chosing variable names that are somewhat confusing. varOpenArgs (with an s at the end) is intended to be a collection that contains one entry for each argument passed in the OpenArgs string, while varOpenArg (with no s at the end) is intended to be used to parse each entry in varOpenArgs into its component parts of Argument Name and Argument Value. In other words, at any point in time, the values contained in varOpenArg will relate to a single entry in varOpenArgs.

That’s it: the entire functionality!

To invoke the form, you’d call the function using code such
as is shown in Listing 6. The form would appear as in Figure 2, you’d type the
password into the input textbox (where the input is masked with asterisks), hit
the OK command button, and the variable strPassword would be set to whatever
was typed in.

Dim strPassword As String
  
  strPassword = PasswordInputBox("What is your password?", "Enter Password")


Listing 6: Using the PasswordInputBox function. The
function returns a String value, so we need to ensure we’ve declared a variable
to accept its value.

How frmInputBox_Password looks when invoked
using the PasswordInputBox function

Figure 2: How frmInputBox_Password looks when invoked
using the PasswordInputBox function. Note how the input is masked with
asterisks.

In the sample database that
accompanies this article, I’ve included a form frmDemonstratePasswordInputBox,
which allows you to provide values for Prompt, Title and Default and invoke the
PasswordInputBox function.

Sample form frmDemonstratePasswordInputBox to
demonstrate our PasswordInputBox function

Figure 3: Sample form frmDemonstratePasswordInputBox to
demonstrate our PasswordInputBox function. You provide values for the three
arguments, then invoke the function by clicking on the exclamation point. The
result returned by the function is displayed at the bottom of the form.

An alternative

An important concept in Access is that of Object-Oriented
Programming. Virtually everything in Access is defined as some kind of an
object (a form, a control on a form, a report and so on), and code runs in
response to events being raised by those objects (a button being clicked on, a
form being resized and so on). There’s no reason not to continue that approach
with the replacement to the InputBox.

Forms in Access are implemented through Classes. Creating a
custom event in Access is as simple as adding a single-line declaration to the
class module, as shown in Listing 7.

[Public] Event procedurename [(arglist)]

Listing 7: Syntax for declaring an event. ProcedureName
follows standard variable naming conventions, and arglist is defined as in any
function or subroutine.

In order for the event to be useful, you need to raise the
event so that the consumer of the class is notified. Again, you do this through
a single line of code, as shown in Listing 8.

RaiseEvent eventname [(argumentlist)]

Listing 8: Syntax for raising an event. EventName is
the name of the event to fire. If there are no values to be returned by the
event, the parentheses are omitted.

To take advantage of this approach, I created a second form (frmInputBox_PasswordWithEvents)
that looks identical to frmInputBox_Password (shown in Figure 1).

Listing 1 showed how little code was involved for frmInputBox_Password
to respond to the user clicking on the OK or Cancel buttons. Listing 9 shows
how using Events doesn’t introduce much more code:

Option Compare Database
Option Explicit

Public Event DataInput(InputValue As String)

Private Sub cmdCancel_Click()

  RaiseEvent DataInput("The Cancel button was selected.")
  DoCmd.Close acForm, Me.Name

End Sub

Private Sub cmdOK_Click()

  RaiseEvent DataInput(Me!txtInput & vbNullString)
  DoCmd.Close acForm, Me.Name

End Sub

Listing 9: The entire code associated with frmInputBox_PasswordWithEvents.
Note that I chose to have the Cancel button return a string to that effect, but
that’s not necessary. (If you don’t want a message returned, simply omit the RaiseEvent
statement).

In order to be able to consume the events raised by frmInputBox_PasswordWithEvents,
you need to host frmInputBox_PasswordWithEvents within another class module.
That means it’s not as convenient to write a simple function (say, PasswordInputBoxWithEvents),
but it’s still pretty straight-forward to use frmInputBox_PasswordWithEvents.

There are three rules governing being able to consume events.

  • The class hosting events must be declared within another class
    module.
  • The object variable created from the class must be declared at a
    module-level, and cannot be declared within a procedure
  • The object variable declaration must include the keyword WithEvents.

Since forms are classes, I’ve created form frmDemonstratePasswordInputBoxWithEvents,
which looks pretty much identical to frmDemonstratePasswordInputBox shown in
Figure 3.

At the very top of the module associated with this form, before
any code for subs or functions, I declare a variable clsInputBox that will be
used to instantiate my input box form and react to its events, as shown in
Listing 10. (The name of the class module associated with any form in Access is
simply Form_ followed by the name of the form)

Option Compare Database
Option Explicit

Dim WithEvents clsInputBox As Form_frmInputBox_PasswordWithEvents

Listing 10: The code to declare an object variable to
be used to instantiate frmInputBox_PasswordWithEvents. Note the inclusion ot
the WithEvents keyword in the declaration.

Figure 4 shows how adding this declaration to the module adds
the variable name (clsInputBox) to the list of available objects in the VB
Editor, and adds the available events when the object is chosen.

A shot of the VB Editor

Figure 4: A shot of the VB Editor. Note how declaring clsInputBox
added it to the list of objects (left-hand combo box) and added its available
events (right-hand combo box)

Invoking frmInputBox_PasswordWithEvents is a little different
from how the PasswordInputBox function invoked frmInputBox_Password. What’s
necessary is to instantiate the clsInputBox variable (using the New keyword). Instantiating
the variable actually opens an instance of the form (albeit invisibly). That means that once the variable has been instantiated, it’s possible to manipulate the objects on the form directly in code, so that there’s no need to fuss with concatenating values in an OpenArgs string. And remember that since the form is not be visible when it’s opened, you need to explicitly set its Visible property to True in order for the form to be seen.
Listing 11 shows what’s required.
Compare this to the Form_Load of frmInputBox_Password (Listing 5): most of the
work that was done there is done here instead.

Private Sub cmdPrompt_Click()

  Me!txtReturned = vbNullString

  If Len(Me!txtPrompt & vbNullString) = 0 Then
    MsgBox "You must provide a value for Prompt.", _
      vbOKOnly + vbCritical
  Else
    Set clsInputBox = New Form_frmInputBox_PasswordWithEvents
    With clsInputBox
      !lblPrompt.Caption = Me!txtPrompt
      If Len(Me!txtTitle & vbNullString) = 0 Then
        .Caption = Application.Name
      Else
        .Caption = Me!txtTitle
      End If
      If Len(Me!txtDefault & vbNullString) > 0 Then
        !txtDefault = Me!txtDefault
      End If
      .Modal = True
      .Visible = True
      .SetFocus
    End With
  End If

End Sub

Listing 11: Code to invoke a password-protected InputBox.
The calling routine takes care of updating the properties of frmInputBox_PasswordWithEvents

Reacting to the event raised is no different from reacting to
any other event. Listing 12 shows how I take the value passed by the event and
display it in text box txtReturned.

Private Sub clsInputBox_DataInput(InputValue As String)

  Me!txtReturned = InputValue

End Sub

Listing 12: The code to respond to an event. Since the
event was declared to pass parameter InputValue, that value is available to us
when consuming the event

That’s pretty much it. You might want to include some
additional clean-up code in the Close event of frmDemonstratePasswordInputBoxWithEvents
to ensure that frmInputBox_PasswordWithEvents isn’t still hidden (see Listing
13), but strictly speaking, it’s not required.

Private Sub Form_Close()

  Set clsInputBox = Nothing

End Sub

Listing 13: The clean-up code when the consuming form
is closed. Access usually does a reasonable job of clean-up on its own, but
it’s never wrong to be explicit!

Assuming you’re going to be using this input box in many
places, you’re probably better off creating a generic routine that you can call
to populate frmInputBox_PasswordWithEvents, rather than replicating that code
in each form. Listing 14 shows such a routine, while Listing 15 shows how you
would use it.

Sub PasswordInputBoxWithEvents( _
    InputBoxForm As Form_frmInputBox_PasswordWithEvents, _
    Prompt As String, _
    Optional Title As String = vbNullString, _
    Optional Default As String = vbNullString _
)

  With InputBoxForm
    !lblPrompt.Caption = Prompt
    If Len(Title) = 0 Then
      .Caption = Application.Name
    Else
      .Caption = Title
    End If
    If Len(Default) > 0 Then
      !txtDefault = Default
    End If
    .Modal = True
    .Visible = True
    .SetFocus
  End With

End Function

Listing 14: More generic code to initialize frmInputBox_PasswordWithEvents.
Note that you need to pass an instantiation of the form as a parameter

Private Sub cmdPrompt_Click()

  Me!txtReturned = vbNullString

  If Len(Me!txtPrompt & vbNullString) = 0 Then
    MsgBox "You must provide a value for Prompt.", _
        vbOKOnly + vbCritical
  Else
    Set clsInputBox = New Form_frmInputBox_PasswordWithEvents
    Call PasswordInputBoxWithEvents(clsInputBox, _
        Me!txtPrompt, _
        Me!txtTitle & vbNullString, _
        Me!txtDefault & vbNullString)
  End If

End Sub

Listing 15: An alternative to Listing 11, using the
generic update code shown in Listing 14. This eliminates the need to repeat code.

Summary

As you’ve seen, neither of the two approaches involve all
that much work, and the fact that you have your own form means you can easily
customize the input box (control the fonts, include a picture, etc.)

Note that all I’ve shown in this article is how to use a text
box with an InputMask set to Password. You’re definitely not limited to that,
though. In fact, if you look in the sample database that accompanies this
article, you’ll see an example that uses a combo box instead of a text box (see
Figure 5), thus ensuring that your users only return valid values.

A sample InputBox utilizing a combo boxshot of
the VB Editor

Figure 5: A sample InputBox utilizing a combo boxshot of
the VB Editor. The code required for this (including letting you use a variety
of different RowSourceType values) is included in the sample database.

Hopefully, this illustrates how to provide control over
events in your code, and you’ll be able to apply it to other situations than
simply a replacement for the InputBox.

»


See All Articles by Columnist
Doug Steele

Doug Steele
Doug Steele
Doug Steele has worked with databases, both mainframe and PC, for many years. Microsoft has recognized him as an Access MVP for his contributions to the Microsoft-sponsored newsgroups.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles