Extending the InputBox function for MS Access Forms

November 12, 2009

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.

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

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

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:

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

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

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")

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.

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

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

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

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

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.

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

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

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

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

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

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.

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