Select Case

VBA Select Case statement is one of the most useful Access VB tools used to program MS Access forms and reports.

The select case visual basic command takes the place of multiple nested if statements and makes your VB code clean and much easier to follow by neatly handling the conditional flow of your VBA programming code.

The syntax of the Visual Basic Select Case statement is:

Select Case Expression
    Case Expression_1
        Statement_1
    Case Expression_2
        Statement_2
    Case Expression_n
        Statement_n
    ...
End Select

Where Expression is a variable passed to the subroutine or a field on a form or within a recordset.

And Expression_1 to n are either constants, ranges of values, series of values, or calculations which resolve to a value.  You can also use alphanumeric comparisons in both the main Expression and the conditional Expressions (1..n).

In the VB select case statement example below we have a report parameter form that has three choices for report output options:

1 - Label report
2 - Datasheet view of query results
3 - A series of statements to prepare an address file

Here is the example select case statement visual basic code:

Private Sub Command5_Click()
Dim pitney As String
Dim sqltext As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim stDocName As String

On Error GoTo Err_Command5_Click

Select Case Me.Output_Options
    Case 1
        stDocName = "R_Daily_Prospect_Labels"
        DoCmd.OpenReport stDocName, acPreview
    Case 2
        DoCmd.OpenQuery "Q_Daily_Prospect_Labels"
    Case 3
        Set db = CurrentDb
        pitney = DLookup("Path", "Q_path_Pitney", 1 = 1)
        pitney = Replace(pitney, "/", "\")
        pitney = Replace(pitney & "\", "\\", "\")
        pitney = pitney & "pitneybowes.mdb"
        DoCmd.RunSQL "delete * from mailmerge in '" & pitney & "'"

        sqltext = "INSERT INTO mailmerge (name,address1,address2,city,state,zip) in '" & pitney & "'" & _
        " select name,address1,address2,city,state,zip from q_daily_prospect_Pitney"

        DoCmd.RunSQL (sqltext)
        Set rst = db.OpenRecordset("select count(name) as icount from mailmerge in '" & pitney & "'")
        rst.MoveFirst
        MsgBox "A total of " & Nz(rst!Icount, 0) & " records were inserted into " & pitney
        rst.Close
        Set rst = Nothing
End Select

Call Mark_As_Sent_Click
Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

More Information and Example for the Visual Basic Select Case Statement:
 

Crosstab Report in Microsoft Access

Select Case j pivot query dynamic crosstab report End Select skip_it: Next rst.Clone Set rst = Nothing End Sub ----------------------------------------- ... www.blueclaw-db.com/report_dynamic_crosstab_field.htm

Visual Basic Change Case

Select Case Iasc(i) Case 65 To 90 ' uppercase letters If ispace = False Then ... End Select new_line = new_line & Chr(Iasc(i)) ' build the new line ... www.blueclaw-db.com/change_name_proper_case.htm

Access Field Validation Visual Basic

Select Case Me.CC_Type ' combo box user selection field Case 1 'amex If IsNull(Me.CC_Number) = True Then MsgBox "You must enter an AMEX number." ... www.blueclaw-db.com/formfieldvalidation.htm

Parameter Query Examples

Select Case G_name Case "Project_ID" Get_Global=GBL_Project_ID ... End Select End Sub. In the form that causes the query (or report) to run, set the global ... www.blueclaw-db.com/accessquerysql/parameter_query.htm