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.
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 SubListing 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 IfListing 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:
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 FunctionListing 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 PasswordListing 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 SubListing 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.
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.
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 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 SubListing 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.
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_PasswordWithEventsListing 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.
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 SubListing 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 SubListing 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 SubListing 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 FunctionListing 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 SubListing 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.
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.