HandsOn Using the Select Case Statement

1. Insert a new module and enter the following TestButtons procedure in the module's Code window.

Sub TestButtons()

Dim question As String Dim bts As Integer Dim myTitle As String Dim myButton As Integer question = "Do you want to preview the report now?" bts = vbYesNoCancel + vbQuestion + vbDefaultButton1 myTitle = "Report"

myButton = MsgBox(prompt:=question, buttons:=bts, Title:=myTitle)

Select Case myButton Case 6

DoCmd.OpenReport "Sales by Year", acPreview

Introduction to Access 2003 VBA Programming

Case 7

MsgBox "You can review the report later." Case Else

MsgBox "You pressed Cancel." End Select End Sub

The first part of the TestButtons procedure displays a message with three buttons: Yes, No, and Cancel. The value of the button selected by the user is assigned to the variable myButton.

If the user clicks Yes, the variable myButton is assigned the vbYes constant or its corresponding value 6. If the user selects No, the variable myButton is assigned the constant vbNo or its corresponding value 7. Lastly, if Cancel is pressed, the contents of the variable myButton equals vbCancel, or 2.

The Select Case statement checks the values supplied after the Case clause against the value stored in the variable myButton. When there is a match, the appropriate Case statement is executed. The TestButtons procedure will work the same if you use constants instead of button values:

Select Case myButton Case vbYes

DoCmd.OpenReport "Sales by Year", acPreview Case vbNo

MsgBox "You can review the report later." Case Else

MsgBox "You pressed Cancel." End Select

You can omit the Else clause. Simply revise the Select Case statement as follows:

Select Case myButton Case vbYes

DoCmd.OpenReport "Sales by Year", acPreview Case vbNo

MsgBox "You can review the report later." Case vbCancel

MsgBox "You pressed Cancel." End Select

2. Run the TestButtons procedure three times, each time selecting a different button. (An error message will pop up when you select Yes. Click End.)

0 0

Post a comment