ComboBox Selection triggers another event

DDEWILDE · April 17th, 2012, 10:51 AM

Hi Everyone,
I am trying to program Access 2007 VBA to make visible an option frame based on the selection in a combo dropdown.
Essentially I need the following to happen;

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.

so far I have something like; (where EventType) is the current name of the combo box but nothing happens when I choose Call in the dropdown.

if me.EventType.value = "Call" then
me.frame16.visible = true
end if

This way if the user clicks that an event was a phone call, the options for the result of that call will be displayed.

It seems very simple but I am not experienced and am not sure of what eventhandler to use; AfterUpdate, MouseDown, MouseUp, etc.

Could someone possibly give me an example of what I need to do, where I need to add the code and why. I really want to learn this.

many thanks for all your kind effort and time spent on my question.

many thanks,

Darren

gjgriffith · April 17th, 2012, 01:33 PM

Hello Darren,

Thank you for posting your question about VBA Case statements here! Specifically, you asked:

Quote:

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:

Code:

    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
        
        Case Else:
            MsgBox "Option not recognized!"
    
    End Select

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!

Sincerely,

DDEWILDE · April 18th, 2012, 06:55 PM

Dear GiGriffith,

Thank you so much for your help. It worked perfectly. There is only one thing I am stuck on, it is related to the last post but possible I might need to start a new thread..I hope you can help me avoid that.

I played about with the form after getting the combo selection to work as you advised. One of the things I wanted to have was a color bar and an icon to represent the selection. I drew the controls as boxes, filled them with the required colors, added the images to represent the choice then added the code to the end of the combo On_Change VBA code you provided to hide unwanted icons and the two other color bars. (On editing I am thinking I need to delete the two other color bars and use VBA to change the back color of one bar depending on the choice.

The prob I am now having is that although the code works perfectly and the icons and color bars change with the selection, they do not change as I scroll through other records. In other words the same icon and the same color bar remain on the next record(s) even though the combo selections were different and I coded the on change event to hide any unecessary icons.

Any ideas what I am doing wrong. My apologies if I have been cheeky trying to ask another question on an already answered question.

Thanks so much again, I really appreciate your help,

Darren