Access/VBA question regarding multiple selections in combo box

[Gina Maylone]

Good morning all,


I hope I'm in the right place. I have read more threads than I care to admit and cannot for the life of me figure this out. I have a multi-select combo box called MentalStatus, with selections such as Alzheimer's, Adjustment Disorder, Anxiety Disorder, Depression, etc. I have an invisible mufti-select combo box called AnxietyDisorderSpec that I want to make visible if Anxiety Disorder is chosen from MentalStatus. It works if ONLY Anxiety Disorder is chosen, but not if other items are chosen as well.


I have tried Case statements, used column values, and a dozen other things with various results. Sometimes I get a "type mismatch" error, or "invalid use of null". I would post my code - but it is so messed up at this point it wouldn't be worth it.
MentalStatus gets its values from another table, which can be changed by a user through a maintenance form. So Anxiety Disorder may not always be the 4th choice. So I've been trying to work with it as a text string, and a text string with wildcards.


If anyone can point me in the right direction I would be so grateful.


Thank you in advance.
Gina

[RuralGuy]

You may have to help me here because I don't think there is such a control as a "Multi-Select ComboBox". A ListBox maybe but not a ComboBox. I could be wrong here.

[Gina Maylone]

Yes, there is now. Thanks!

[rzw0wr]

RiralGuy, he only place I have seen to make a milti-select combo box is a lookup field.

There may be other places, however I have not found them.

Dale

[RuralGuy]

Thanks Dale. I'll do some research.

@Gina: Are you using Lookup Fields? http://access.mvps.org/access/lookupfields.htm

[Gina Maylone]

Table: ResidentMentalStatus
Field1: ResidentID
Field2: MentalStatus
Data Type: Short Text
Row Source Type: Table/Query
Row Source: SELECT [MntMentalStatus].[MentalStatus] FROM MntMentalStatus;
Bound Column: 1
Allow Multiple Values: Yes

On my form, the combo box lists all of the Mental Statuses with a check box next to each one. You can select one or many.

I've seen many posts that people question the availability of multi-select comboboxes - but they are here, I assure you. Perhaps since V 2007.

Thanks!

[RuralGuy]

Not ever having used Lookup Fields I will have to plead ignorance. ComboBoxes on forms are just fine but Lookup Fields tend to just confuse the developer.

[RuralGuy]

@Gina: What code is in the AfterUpdate event of your ComboBox?

[Gina Maylone]

Good morning all,


I hope I'm in the right place. I have read more threads than I care to admit and cannot for the life of me figure this out. I have a multi-select combo box called MentalStatus, with selections such as Alzheimer's, Adjustment Disorder, Anxiety Disorder, Depression, etc. I have an invisible mufti-select combo box called AnxietyDisorderSpec that I want to make visible if Anxiety Disorder is chosen from MentalStatus. It works if ONLY Anxiety Disorder is chosen, but not if other items are chosen as well.


I have tried Case statements, used column values, and a dozen other things with various results. Sometimes I get a "type mismatch" error, or "invalid use of null". I would post my code - but it is so messed up at this point it wouldn't be worth it.
MentalStatus gets its values from another table, which can be changed by a user through a maintenance form. So Anxiety Disorder may not always be the 4th choice. So I've been trying to work with it as a text string, and a text string with wildcards.


If anyone can point me in the right direction I would be so grateful.


Thank you in advance.
Gina

I hope I'm replying correctly. I am embarrassed to even post this, but this is what I have now which I am getting the error "Type Mismatch"

Select Case True
Case Me.MentalStatus Like "*AnxietyDisorder*"
Me.AnxietyDisorderSpec.Visible = True
Case Else
Me.AnxietyDisorderSpec.Visible = False
End Select

[RuralGuy]

Any chance you could provide a sample db that demonstrates the issue while not including private data?

[orange]

Here is a sample of the Select Case statement. I don't think your syntax is what you really want.(my best guess)
http://www.techonthenet.com/access/f...anced/case.php

[Gina Maylone]

Any chance you could provide a sample db that demonstrates the issue while not including private data?

MentalStatus.zip Again, I hope I did this correctly. You will see my Event Procedure is a mess. Thanks so much!!

[RuralGuy]

Boy are these things weird to work with. Try this db.

[Gina Maylone]

OMG!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! IT'S PERFECT!! YOU'RE BRILLIANT!!!

And yes, they are weird to work with. I just found out I can't move the data from one table to another. Unfortunately, it's the only way I can figure out how to allow the user the ability to maintain the source table and have multiple selections.

Again - thank you from the bottom of my heart!! I hope you have a fantastic afternoon!!

[RuralGuy]

I'm still not a fan of these Multi-Select ComboBoxes. AFAIK they are not supported by SQL server and are avoided by most developers. I marked this thread as Solved for you.