Microsoft Access / Visual Basic: Case Syntax

When to Use a Case Statement

How can you formulate Microsft Access/Visual Basic Case syntax? If you're only dealing with a single input variable that can control many

wikimedia commons, logo, ZyMos

possible outputs depending on its value, then your first instinct can be to use an If statement, but you can improve readability and efficiency of your code by making use of the Case statement.

Let's say you have a form with an option group on it (the boxes that contain several choices with radio buttons next to them, so you can select a single choice). When you created the group, you gave it a name (let's assume it's called optGroup) and each radio button has a value assigned to it. Now add a button to the form, and you want to code that button so that when you click on it, something different happens depending on which option the user has selected. This is where the Case statement comes in.

Case Statement Syntax

The Visual Basic Case syntax is very simple and looks like this:

Select Case <VarName>

Case <Value1>

<Code to run if VarName = Value1>

Case <Value2>

<Code to run if VarName = Value2>

Case Else (optional)

<Code to run if VarName doesn't equal any of the other values>

End Select

Case Statement Example

Let's go back to our first section, where we had an option group called optGroup. Let's say it has three radio buttons, which have values 1, 2 & 3. You could then code your button as follows:

intGroup = me!optGroup – This passes the value from the option group into a variable called intGroup

Select Case intGroup

Case 1

msgbox "You chose option 1"

Case 2

msgbox "You chose option 2"

Case 3

msgbox "You chose option 3"

Case Else

msgbox "Please choose an option"

End Select

Note that you should use the Case Else statement depending on what values might be passed to the variable you are working on. With an option group, you can set a default value, which means that apart from in the event of an error, it would be impossible for the variable to have a value that wasn't 1, 2 or 3. You could always use Case Else to cover the default option you'd like to choose in the event of an error.

Other Uses of the Case Statement

The Case statement doesn't have to take single values. Using our option group example, we could have the following code.

Select Case intGroup

Case 1, 2

msgbox "You chose one of the first two options"

Case Else

msgbox "You chose option 3 (or something went wrong)"

End Select

An equivalent piece of code would be to replace the line Case 1, 2 with

Case Is <= 2

or

Case 1 To 2

Obviously, you're not just limited to taking the output of an Option Group; so now that you know how to use it, you can start to make use of the Case statement in VBA.

Image and Resource

Image: Wikimedia Commons/ZyMos

Resource: Based on the writer's experience