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.
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
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
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
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:
Required. String expression displayed as the message in
Optional. String expression displayed in the title bar of
Optional. String expression displayed in the text box as
Optional. Numeric expression that specifies, in twips, the
Optional. Numeric expression that specifies, in twips, the
Optional. String expression that identifies the Help file
Optional. Numeric expression that is the Help context
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
The entirety of my PasswordInputBox function is shown in
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
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.
Figure 2: How frmInputBox_Password looks when invoked
using the PasswordInputBox function. Note how the input is masked with
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
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 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
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
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
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.
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.
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.
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.