Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Inputbox and Simple Menus

Introdu8ction

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 exiting objects like MsgBox(). Probably its usage is not properly understood and it doesn't deserve this neglect. The usage of 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 like OK, Cancel, Retry, Yes, No etc. MsgBox cannot be programmed to make selections from user defined Options like the facility provides by InputBox().

We have learned how to use Office Assistant with preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using Office Assistant with Check-Boxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and needs some time to understand the intricacies. 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 simple usage of InputBox() function Syntax is given below:

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

The Title and Default Values 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 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 on a Command Button Click EventProcedure 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 removed from the Title Bar except 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 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. . . statement 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 to 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 want to get out of the Menu normally.

We have inserted 1 as 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:

  1. to make it a better looking real Menu
  2. to ignore the Cancel Command Button and Window Close Title Bar control clicks.
  1. 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"
          
  2. We will put the InputBox() Function within a Do While . . . Loop with a condition so that the Function will keep repeatedly executing if the user doesn't make a valid selection from the Menu and discourage her from Clicking 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 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. . . statement 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 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
Share:

1 comment:

  1. Really nice laid out site with great code examples.
    Marking this as a favorite!!
    Thanks for sharing.

    (Virtual World not really my web site, but one that is enjoyable)

    ReplyDelete

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Key Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Item Macros Menus RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference TreeView Control Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers ImageList Control Import Labels List ListView Control Logo Macro Mail Merge Main Form Memo Monitoring Nodes Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code