Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 12, 2009

Extending the InputBox function for MS Access Forms

By Doug Steele

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", _

  If CurrentProject.AllForms("frmInputBox_Password").IsLoaded Then
' The OK button was clicked. Retrieve the value from the input box.
' 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 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.


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.


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.


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.


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.


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.


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.


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

  If CurrentProject.AllForms("frmInputBox_Password").IsLoaded Then
    PasswordInputBox = Forms("frmInputBox_Password")!txtInput
    DoCmd.Close acForm, "frmInputBox_Password"
    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

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
    Set clsInputBox = New Form_frmInputBox_PasswordWithEvents
    With clsInputBox
      !lblPrompt.Caption = Me!txtPrompt
      If Len(Me!txtTitle & vbNullString) = 0 Then
        .Caption = Application.Name
        .Caption = Me!txtTitle
      End If
      If Len(Me!txtDefault & vbNullString) > 0 Then
        !txtDefault = Me!txtDefault
      End If
      .Modal = True
      .Visible = True
    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
      .Caption = Title
    End If
    If Len(Default) > 0 Then
      !txtDefault = Default
    End If
    .Modal = True
    .Visible = True
  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
    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.

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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM