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

Trying to use SELECT CASE for multiple selections..

P: 78
In access 2003, on a simple form, I am trying to base the recordsource of a combobox on two other combobox's data.

Here is the code I've tried and it did not work (except for the PN Cross/Info).

The Combo boxes names are: Product Type, Combo33 and Issue, where Issue is what should populate based on what is chosen in Product Type and Combo33.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_AfterUpdate()
  2. Select Case True
  3.  
  4. Case (Me.[Product Type] = "1. Compressors" And Me.[Combo33] = "Troubleshoot")
  5.     Issue.RowSource = "tbl1TS"
  6.  
  7. Case (Me.[Product Type] = "2. Actuators" And Me.Combo33 = "Troubleshoot")
  8.     Issue.RowSource = "tbl2TS"
  9.  
  10. Case (Me.[Product Type] = "3. Valves" And Me.Combo33 = "Troubleshoot")
  11.     Issue.RowSource = "tbl3TS"
  12.  
  13. Case (Me.[Product Type] = "4. Reservoirs" And Me.Combo33 = "Troubleshoot")
  14.     Issue.RowSource = "tbl99NA"
  15.  
  16. Case (Me.[Product Type] = "5. Slacks" And Me.Combo33 = "Troubleshoot")
  17.     Issue.RowSource = "tbl5TS"
  18.  
  19. Case (Me.[Product Type] = "6. Warning Devices" And Me.Combo33 = "Troubleshoot")
  20.     Issue.RowSource = "tbl99Leak"
  21.  
  22. Case (Me.[Product Type] = "8. Air Dryers" And Me.Combo33 = "Troubleshoot")
  23.     Issue.RowSource = "tbl8TS"
  24.  
  25. Case (Me.[Product Type] = "9. Fan Drives" And Me.Combo33 = "Troubleshoot")
  26.     Issue.RowSource = "tbl99NA"
  27.  
  28. Case (Me.[Product Type] = "13. ABS" And Me.Combo33 = "Troubleshoot")
  29.     Issue.RowSource = "tbl13TS"
  30.  
  31. Case (Me.[Product Type] = "15. Electronics" And Me.Combo33 = "Troubleshoot")
  32.     Issue.RowSource = "tbl99Leak"
  33.  
  34. Case (Me.[Product Type] = "18. Systems" And Me.Combo33 = "Troubleshoot")
  35.     Issue.RowSource = "tbl18TS"
  36.  
  37. Case (Me.[Product Type] = "20. Hydraulics" And Me.Combo33 = "Troubleshoot")
  38.     Issue.RowSource = "tbl20TS"
  39.  
  40. Case (Me.[Product Type] = "21. S-Cams" And Me.Combo33 = "Troubleshoot")
  41.     Issue.RowSource = "tbl21TS"
  42.  
  43. Case (Me.[Product Type] = "23. Air Disc" And Me.Combo33 = "Troubleshoot")
  44.     Issue.RowSource = "tbl23TS"
  45.  
  46. Case (Me.[Product Type] = "99. Misc" And Me.Combo33 = "Troubleshoot")
  47.     Issue.RowSource = "tbl99NA"
  48.  
  49. Case (Me.Combo33 = "PN Cross/Info")
  50.            Issue.RowSource = "tblPNCROSS"
  51.  
  52. End Select
  53.  
Thanks!
Jan 26 '10 #1
Share this Question
Share on Google+
11 Replies


P: 53
Instead of using AND try using a comma

Expand|Select|Wrap|Line Numbers
  1. Select Case True 
  2.  
  3. Case Me.[Product Type] = "1. Compressors", Me.[Combo33] = "Troubleshoot" 
  4. Issue.RowSource = "tbl1TS" 
  5.  
  6. End Select
  7.  
Hope this helps

Cheers

Leon
Jan 27 '10 #2

P: 78
Echidna, thanks for the tip. I tried it, but nothing populates in combobox "Issue"??
I select something from the dropdown in "Product Type" and "Combo33" and when I click the dropdown arrow for "Issue", a blank white box displays below it (as if it found nothing).
Could it be that I'm doing this in Combo33's AfterUpdate??
Jan 27 '10 #3

P: 53
Hi,

Is there anything within table tbl1TS?

Also are the number of columns within tbl1TS identical within those specified in the Issue combobox?

also you could try throwing a requery at the combobox after the select statement.
Jan 27 '10 #4

P: 53
Hi,

disregard that one, I got the wrong end of the stick there...

Do you want to have the rowsource set as "tbl1TS"; "tbl99NA" etc and wanting the issue to display this one issue, or only show that particular entry when the combobox is activated?

by setting the rowsource, you are limiting this down to 1 entry.

if you want the former me.issue = "tbl1TS"
Jan 27 '10 #5

P: 78
tbl1TS and all the others are tables that hold several options.

So when I choose a choice in "Product Tye" (example: 1. Compressors), AND choose a choice from "Combo33" (example: Troubleshoot), then I need "Issue" combobox to display the contents (or choices) in "tbl1TS".

Should I have it like this:

Expand|Select|Wrap|Line Numbers
  1. Select Case True  
  2.  
  3. Case Me.[Product Type] = "1. Compressors", Me.[Combo33] = "Troubleshoot"  
  4. Me.Issue = "tbl1TS"  
  5.  
  6. End Select 
  7.  
Jan 27 '10 #6

P: 78
Although I think that would just display the text "tbl1TS", right?
Jan 27 '10 #7

P: 53
Yes it would.

So something like issue.rowsource = "Select columnname1, columnname2 from Table"

make sure the number of columns you are selecting within the rowsource = the number of columns within the combobox

Cheers

Leon
Jan 27 '10 #8

P: 78
That sounds like it would only be displaying the fields of tbl1TS. The fileds need to be in dropdown form and the user would have to be able to choose one of them.
Just like they did in "Product Type" and "Combo33".

Or am I not understanding correctly?
Jan 27 '10 #9

P: 53
That would display the data within the fields selected
Jan 27 '10 #10

P: 78
Echidna, Thank you for your assistance! It turns out that I'm an idiot and you helped me realize it! I built the tables wrong so that is why nothing was populating in the "Issues" combobox!

Thanks for you help!!!
Jan 27 '10 #11

P: 53
No Probs :)

Glad I could help

Cheers

Leon
Jan 28 '10 #12

Post your reply

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