SELECT CASE Statement

Select Case Statement

The Select Case statement can only be used in VBA, and is very similar to an if-then-else statement in other languages. It is great for use with an option box where a user can select one of many options and then click a button to perform an action, open a form, etc. It is also very useful in other instances. The Select Case statement holds the value or expression to be evaluated. The lines of Case " " statements are conditions that are compared to the select case value. If one of these conditions is true, then the code below will execute.

This example uses a button that is clicked by a user when they want to view a completed purchase order. The Select Case is looking at a listbox that is located on a form. If the 8th column of the listbox (listbox columns always start at 0), has a order status equal to any of the below conditions (open, requested, issued, hold, cancelled....) the associated code will execute.

For example if that order status of the item selected in the listbox is Issued, then 
frmReceiving_View will open.


Private Sub btnReceiving_Click()

Select Case Forms!frmMain!lstbx_Lookup.Column(7)

Case "Open"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "Requested"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "Issued"
DoCmd.OpenForm "frmReceiving_View"

Case "Partial"
DoCmd.OpenForm "frmReceiving_View"

Case "Complete"
DoCmd.OpenForm "frmReceiving_View"

Case "RFQ"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "HOLD"
DoCmd.OpenForm "frmReceiving_View"

Case "Cancelled"
MsgBox "Receiving detail not available when PO is cancelled."
Exit Sub

End Select

End Sub


Is, To, Comma Delimited, and CASE Else

This statement sets value of a string variable based on a form on a field called "age". If [AGE] is equal to any of the case statements then the strAgeGroup variable will be set. Otherwise strAgeGroup will be set to "Other". You can see that the select case statement uses the Is keyword, To Keyword, and commas to delimited values associated with case.

SELECT CASE  [age]

Case Is < 3
     strAgeGroup = "Toddler"

Case 3 to 9
     strAgeGroup = "Child"

Case 10,11,12,13
     strAgeGroup = "Tween"
Case 14 to 17
     strAgeGroup = "Teen"

Case 18 to 25
     strAgeGroup = "Youg Adult"

Case 26 to 64
     strAgeGroup = "Adult"

Case 65 to 110
     strAgeGroup = "Senior"

Case Else
     strOther = "Other"

END SELECT