Creating an If/Then/Else Statement for a combo box that allows multiple values in VBA

[MicaK]

Good Morning,

I am new to this forum, and extremely new to VBA, so there may be a very simple explanation to this. I also apologize if I am giving you and excessive amount of detail. I have a table, tblAdmissions. One of the columns is for Certification and was set up as a lookup field that allows multiple values to be selected. In my form, the label for this field is called lblCertification, and the combo box is called cboCertification. I am trying to write an If/Then/Else statement that will turn the fore color in the label and the fore color combo box red if a certain value is selected. I wrote the following code:

    If Me.cboCertification = "Other" Then
    Me.lblCertification.ForeColor = vbCyan
    Else
    Me.lblCertification.ForeColor = vbRed
    End If

When I try to use this code I get the following message, "Run time error '13': Type mismatch." This basic formula has worked everywhere else in the form, but I suspect since this combo box allows multiple values there is something I am not taking into account.
I am sorry if this is a ridiculously easy question. Thank you for any help you are able to provide.

[mshmyob]

Data Type mismatch has to do with the data type you are checking for.

I am assuming that your combo box has 2 columns at least, with the key column hidden. Therefore when you say check for "Other" you are comparing the key column (which is probably a number) to a text value ("Other")

Use the column property (Me.cboCertification.column(0) or Me.cboCertification.column(1)) to compare the proper values.

Column(0) would refer to the hidden key column and column(1) would refer to your textual column that you actually are seeing in the combo box.

cheers,

[NeoPa]

I suspect Mshmyob is absolutely right about your issue. An alternative solution would be to compare your ComboBox value with the numeric equivalent of "Other" of course. The type of the value of the ComboBox will be the same as the field in the BoundColumn property. If the BoundColumn is 1, then the first field in the record source determines the Value of the control. If it's 2 then the second does, etc.