Trying to use SELECT CASE for multiple selections..

[WannabePrgmr]

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.

Private Sub Combo33_AfterUpdate()
Select Case True

Case (Me.[Product Type] = "1. Compressors" And Me.[Combo33] = "Troubleshoot")
    Issue.RowSource = "tbl1TS"

Case (Me.[Product Type] = "2. Actuators" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl2TS"

Case (Me.[Product Type] = "3. Valves" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl3TS"

Case (Me.[Product Type] = "4. Reservoirs" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl99NA"

Case (Me.[Product Type] = "5. Slacks" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl5TS"

Case (Me.[Product Type] = "6. Warning Devices" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl99Leak"

Case (Me.[Product Type] = "8. Air Dryers" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl8TS"

Case (Me.[Product Type] = "9. Fan Drives" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl99NA"

Case (Me.[Product Type] = "13. ABS" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl13TS"

Case (Me.[Product Type] = "15. Electronics" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl99Leak"

Case (Me.[Product Type] = "18. Systems" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl18TS"

Case (Me.[Product Type] = "20. Hydraulics" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl20TS"

Case (Me.[Product Type] = "21. S-Cams" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl21TS"

Case (Me.[Product Type] = "23. Air Disc" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl23TS"

Case (Me.[Product Type] = "99. Misc" And Me.Combo33 = "Troubleshoot")
    Issue.RowSource = "tbl99NA"

Case (Me.Combo33 = "PN Cross/Info")
           Issue.RowSource = "tblPNCROSS"

End Select

Thanks!

[Echidna]

Instead of using AND try using a comma

Select Case True 
  
Case Me.[Product Type] = "1. Compressors", Me.[Combo33] = "Troubleshoot" 
Issue.RowSource = "tbl1TS" 

End Select

Hope this helps

Cheers

Leon

[WannabePrgmr]

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??

[Echidna]

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.

[Echidna]

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"

[WannabePrgmr]

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:

Select Case True  
  
Case Me.[Product Type] = "1. Compressors", Me.[Combo33] = "Troubleshoot"  
Me.Issue = "tbl1TS"  
  
End Select

[WannabePrgmr]

Although I think that would just display the text "tbl1TS", right?

[Echidna]

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

[WannabePrgmr]

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?

[Echidna]

That would display the data within the fields selected

[WannabePrgmr]

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!!!

[Echidna]

No Probs :)

Glad I could help

Cheers

Leon