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:

I think InputBox() function is one of the few things that we come across when start learning VBA and discard it later altogether when we see more exciting objects like MsgBox(). Probably its usage is not properly understood and it doesn't deserve this neglect. The usage of the Inputbox() function is very simple like MsgBox() but seldom used in programs to gather user responses. Instead, MsgBox() is the most preferred control, which has a variety of options available to configure it with different options of Command Buttons. But these options are OK, Cancel, Retry, Yes, No, etc. MsgBox cannot be programmed to make selections from user-defined Options like the facility provided by the InputBox() Function.

We have learned how to use the Office Assistant with the preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using the Office Assistant with CheckBoxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and need some time to understand their various ways of usage. But, once the program is in place the usage is very simple and can be inserted freely in your Code across projects, when placed in a library database and when linked to your other projects.

A Simple Example

Coming back to the Inputbox() function; let us look into a simple example in a sub-routine. A Report Command Button on the Main Switchboard when clicked; a small menu is displayed asking the User whether she would like to get (1) Report Preview or (2) Print the Report directly to the Printer or (3) Exit if there is a change of mind.

The simplest usage of InputBox() function Syntax is given below:

X = InputBox("Message Text","Title",Default Value)

The Title and Default Value parameters are Optional. There are other optional values as well. You may check for them in the Microsoft Access Help Documents.

The Message Text is displayed on the body of the Form and the Title text appears on the Title Bar of the Control. We have used 1 as the third Parameter (Default Value) and that value is inserted in the Text Box as shown in the following example image:

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 as per the Options provided, the selected value is tested and executes the actions programmed in the Select Case. . .End Select statements.

Note The Inputbox() function brings up a Form with its Border Style Property Value set as Dialog so that the Minimize/Maximize & Restore Buttons are removed from the Title Bar except for the Window Close control. You are not allowed to click on any other Form or Objects in the Application window before you are done with the InputBox() Function, indicating that this Form's Modal Property is set to Yes. We have familiarized the usage of Pop up and other Property settings of the Form in our earlier discussions on Form Properties in the following Articles:

Taking Care of Pitfalls

While using Inputbox() this way in programs we must take care of a few things to avoid some undesirable side effects and pay attention to give the Menu a better look as well.

First, we will take the side effects part. We have provided Option 3 to close the Dialog Box normally if the User has a change of mind after bringing up the Menu. But, she may ignore option 3 in the Menu and may use the Window Close control on the Title bar or click on the Cancel Command Button instead; that can invite trouble in the Code.

Since we have defined the Option Values in the Menu as Integer (1,2 and 3) naturally we will think about using an Integer Type Variable to record the response of the User. But, the User may click on the Cancel Command Button or Window Close Title Bar control, without selecting any of the options displayed, to close the Window. In that case the InputBox() function will return an empty String ("") as response. If we have defined an Integer Variable to accept the returned value, then this will end up with a Type Mismatch Error and the program will crash.

We can use a string variable to accept the Empty string result to avoid this problem and the Select Case . . . End Case, statement structure will work without modification even though we are testing for Integer values rather than in Case "1" style. A reasonable Variable Type selection to record the user response in these circumstances is a Variant Type that can accept any data type.

With a little trick, we can make the program ignore the Cancel Button and Window Close Title Bar control Clicks and force the user to make a selection from the Menu Options only if she wants to get out of the Menu normally.

We have inserted 1 as the third Optional Parameter Value to take care of the OK Command Button Click by the User just in case she doesn't type a selection value from the Menu Options and simply hits the OK Button. If she types one of the Option values in the Text Box and hits the Enter key, as we expect her to do, that will return the value selected into the varResponse Variable, and the InputBox() control will close normally.

We will modify the above Code:

To make it a better-looking real Menu.
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 put the InputBox() Function Code within the Do While . . . Loop structure, which will keep repeating the statements within the Loop if the user doesn't make a valid selection from the Menu. The user will not be allowed to click the Window Close or Cancel Buttons to get out of the Dialog Box.

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:

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 alphabets instead, like

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

Yes, we can with 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 alphabets as Options are 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