Thank you for posting your question about VBA Case statements here! Specifically, you asked:
when a certain selection is made in the comboBox, an option Frame will become visible or another action based on another choice, etc. I know about the CASE statement but not sure how to code it.
Could someone possibly give me an example of what I need to do, where I need to add the code and why.
Ok, so first, it is important to understand what TYPE of data the ComboBox control returns for this example, because it can return the value that it shows, or a value that is an ID of the value it is showing, depending on how the ComboBox is setup. In this example, the ComboBox I'm using only displays a value list, but depending on what data your ComboBox is attached to, this solution could be slightly different.
As for the solution, if you make a new form in Design view in Access and then drop a ComboBox on to the form, it will launch the ComboBox wizard. In this case, I chose the "Value List" option on the wizard and typed in the values "Value 1" and "Value 2" for the values to be displayed in the ComboBox. This means that the ComboBox will return these specific string values (which I will show below). I then added 2 more frame controls to the form, named "Frame1" and "Frame2". Now that we have all of the controls we need on the form for this example. Next, click on the ComboBox control to select it, and then open the Property Sheet (F4) to see the properties for the control. On the "Event" tab of the property sheet, find the "On Change" event and click the "..." button for it, and choose the "Code Builder" option on the Build Event dialog. This will open VBE and create an "On Change" Sub for the ComboBox control. Place the following code inside of this "On Change" Sub:
Dim strOption As String
' Get the value selected from the ComboBox, in this case a String
strOption = Me.Combo0
' Use the Case statement to make the proper Frame visible, based on the
' value selected in the ComboBox.
Select Case strOption
Case "Value 1":
Me.Frame1.Visible = True
Me.Frame2.Visible = False
Case "Value 2":
Me.Frame1.Visible = False
Me.Frame2.Visible = True
MsgBox "Option not recognized!"
Now you should be ready to run the form. Save everything and switch the new form to Form view and try selecting values in the ComboBox. You should see the Frame controls being switched between visible and invisible!
Anyway, I hope that all helps, but if you have any other questions or need more help with this example, please just let me know! Good luck and thanks again for posting!