469,282 Members | 1,758 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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

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,

2 7719
mshmyob
904 Expert 512MB
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
32,173 Expert Mod 16PB
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.

Similar topics

3 posts views Thread by Indraneel Sheorey | last post: by
7 posts views Thread by Harris | last post: by
13 posts views Thread by jkimbler | last post: by
Kelicula
3 posts views Thread by Kelicula | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.