By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,186 Members | 1,530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,186 IT Pros & Developers. It's quick & easy.

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

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1.     If Me.cboCertification = "Other" Then
  2.     Me.lblCertification.ForeColor = vbCyan
  3.     Else
  4.     Me.lblCertification.ForeColor = vbRed
  5.     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.
Sep 14 '09 #1

✓ answered by 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,

Share this Question
Share on Google+
2 Replies


mshmyob
Expert 100+
P: 904
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,
Sep 14 '09 #2

NeoPa
Expert Mod 15k+
P: 31,766
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.
Sep 14 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.