Inputbox and Simple Menus ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We have already learned how to create menus and toolbars through the following articles:

The InputBox() function is one of the first tools we encounter when learning VBA, but it is often discarded later in favor of more “exciting” objects like MsgBox(). This neglect is unfortunate because the InputBox() function is simple to use—just like MsgBox()—and is a useful way to gather user input directly.

While MsgBox() is the more commonly used option and offers a variety of command buttons (OK, Cancel, Retry, Yes, No, etc.), it cannot be programmed to allow selections from user-defined options—a capability that InputBox() provides.

We have also explored how to use the Office Assistant with a preferred animation character as an alternative to MsgBox(), collecting user responses through checkboxes or option balloon menus. Admittedly, the VBA routines involved in these methods are more complex and take some time to fully understand. However, once implemented, these routines are easy to use and can be freely incorporated into your code across projects—especially if stored in a library database and linked to other projects.

A Simple Example

Coming back to the InputBox() function, let’s look at a simple example used within a subroutine.

Imagine a Report Command Button on the Main Switchboard: when the user clicks this button, a small menu appears asking whether they would like to:

Preview the Report,
Print the Report directly to the printer, or
Exit, if they change their mind.

The simplest form of the InputBox() function syntax is:


X = InputBox("Message Text", "Title", DefaultValue)

Message Text → Appears in the body of the dialog box.
Title → Appears in the title bar of the InputBox window.
DefaultValue → (Optional) The initial value that appears in the text box.

The Title and Default Value parameters are optional. (There are other optional parameters as well—refer to the Microsoft Access Help documentation for details.)

In our example, we’ll use 1 as the third parameter (Default Value). This means that when the InputBox appears, the value 1 will already be displayed in the text box, as shown in the example image below.

Right-click to open Large Image in New Tab/Window.

The Code that brings this Form up with a Command Button Click Event Procedure is given below.

Private Sub cmdRpt_Click()
Dim varResponse as Variant

varResponse = InputBox("(1) Print Preview (2) Print (3) Exit", "Select 1/2/3",1)

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

When the user makes a selection from the options provided, the chosen value is evaluated using the Select Case... End Select statement, and the corresponding action is executed.

Note:
The InputBox() function displays a small form whose Border Style property is set to Dialog. As a result, the Minimize, Maximize, and Restore buttons are removed from the title bar, leaving only the Close button.

Additionally, while the InputBox is open, you cannot click on any other form or object within the Access application window. This behavior indicates that the InputBox operates with its Modal property set to Yes—it must be closed or completed before returning control to other parts of the application.

We have already explored the usage of Pop-up, Modal, and other related form properties in our earlier discussions on Form design and behavior, particularly in the following articles:

Taking Care of Pitfalls

While using the InputBox() function this way in programs, we must take care to avoid a few undesirable side effects and also pay attention to improving the appearance of the menu.

Let us first deal with the side effects. We have provided Option 3 to close the dialog box normally if the user changes her mind after opening the menu. However, she might ignore Option 3 and instead use the Close button on the title bar or click Cancel, and that can cause problems in the code.

Since the option values in the menu are defined as integers (1, 2, and 3), it is natural to use an Integer variable to capture the user’s response. But if the user closes the window using the Cancel button or the Close control, the InputBox() function returns an empty string (""). If this value is assigned to an Integer variable, a Type Mismatch error will occur, and the program will crash.

To avoid this, we can declare the variable as a String, which can safely receive an empty string. The Select Case... End Select structure will still work correctly even though we are testing for numeric values, because VBA automatically handles the type conversion in this context. A better alternative, however, is to use a Variant variable, which can accept any data type and gives more flexibility.

With a small trick, we can also make the program ignore Cancel and Close button clicks, forcing the user to choose one of the defined menu options if she wants to exit the menu normally.

We have inserted 1 as the third optional parameter value in the InputBox() function to handle the case where the user clicks OK without entering anything in the text box. If she types one of the option values and presses Enter, as expected, that value will be returned into the varResponse variable, and the InputBox() will close normally.

We will modify the above Code:

To make a real Menu with a good appearance.
To ignore the Cancel Command Button and Window Close Title Bar control clicks.

To prepare the Menu, we will define a string Variable and write the following expression before inserting it into the InputBox() Function:

Dim strMenu as String

    strMenu = "1. Report Preview" & vbCr & vbCr

    strMenu = strMenu & "2. Print Report" & vbCr & vbCr

    strMenu = strMenu & "3. Exit"

We will place the InputBox() function inside a Do While...Loop structure. This ensures that the menu keeps reappearing until the user makes a valid selection from the available options. In this setup, the user will not be able to exit the dialog box by clicking the Cancel button or the Close control on the title bar. The loop will continue to prompt the user until a valid response—such as 1, 2, or 3—is entered.

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3

       varResponse = InputBox(strMenu, "Select 1/2/3",1)

  Loop

After the above changes, the Menu will look like the following image:

Right-click to open Large Image in New Tab/Window.

Modified VBA Code

The modified code is given below:

Private Sub cmdRpt_Click()
'------------------------------------------------------------
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------
Dim varResponse As Variant
Dim strMenu As String

strMenu = "1. Report Preview" & vbCr & vbCrstrMenu = strMenu & "2. Print Report" & vbCr & vbCrstrMenu = strMenu & "3. Exit"

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3
    varResponse = InputBox(strMenu, "Select 1/2/3", 1)
Loop

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

Probably the next question that comes into one's mind is, do we have to use the Menu Options always in the form 1,2,3, etc., can we use alphabet instead, like

R. Report Preview
P. Print Report
E. Exit?

Right-click to open Large Image in New Tab/Window.

Yes, we can with a few changes in the Code as given below:

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr

strMenu = strMenu & "E. Exit"

varResponse= ""

Do While instr(1, "RPE",varResponse)=0 or len(varResponse)=0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")

Loop

The Select Case... End Select statements must be changed to:

Select Case varResponse
  Case "R"

      DoCmd.OpenReport "Orders", acViewPreview

  Case "P"

      DoCmd.OpenReport "Orders", acViewNormal

  Case "E"

      Exit Sub

End Select

Option Alphabet Values

The changed Code with alphabet as Options is given below:

Private Sub cmdRpt_Click()
'------------------------------------------------------------
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------
Dim varResponse As VariantDim strMenu As String

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr
strMenu = strMenu & "E. Exit"

varResponse = ""

Do While InStr(1, "RPE", varResponse) = 0 Or Len(varResponse) = 0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")
Loop

Select Case varResponse
    Case "R"
        DoCmd.OpenReport "Orders", acViewPreview
    Case "P"
        DoCmd.OpenReport "Orders", acViewNormal
    Case "E"
        Exit Sub
End Select

End Sub