%dim crumb crumb="Select Case" Crumb2="VBA Select Case" crumb1="Visual Basic Select Case" %>
Visual Basic Tutorials:
|Get your Select Case statement
running in minutes. Give us a call and we will solve the problem or
there is no charge!
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 Access Case statement is:
Select Case Expression
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 for each Case. You can also use alphanumeric comparisons in both the main Expression and the conditional Expressions (1..n).
In the VBA 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 Access Case statement used in 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
stDocName = "R_Daily_Prospect_Labels"
DoCmd.OpenReport stDocName, acPreview
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"
Set rst = db.OpenRecordset("select count(name) as icount from mailmerge in '" & pitney & "'")
MsgBox "A total of " & Nz(rst!Icount, 0) & " records were inserted into " & pitney
Set rst = Nothing
More Information and Example for the VBA Select Case Statement:
MS Access 2000 Through 2016 and Office 365 & Sharepoint
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>