Using Drop Down Box Menu to Select Data

[akrshaw]

I'm not sure how to say this concisely...so bear with me please.

I need to create a menu that has three combo boxes that are linked to each other for the following:

Unit # TS # Adjoining TS #
001 001 2343-001
001 002 2346-001
001 002 2234-002
002 001 2847-001
042 001 EXISTING
084 001 VENDOR

I would like to be able to have the full list of Units in the first box, then the second box limited to only those TS that are related to that box show, then filter that down to the Adjoining box.

Resulting in a list for only that Unit, TS # and all the Adjoining.

Can someone point me in the right direction? I did this YEARS ago...but I've slept since then and aged a lot!~

Thanks!~

 

[dhookom]

Check Forms FAQs

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

Well, I have the following in a combined combox:

Criteria:
Unit # TS # Adjoining TS #

Which is in the Form Header....

Then I added a subform with the table...

Just stumped on how to link the two?

 

[dhookom]

I'm lost. You first mention 3 combo boxes and now seem to have one combined combo box. There was no mention earlier of a subform.

Can you please give us enough information about your requirements?

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

So, sorry I'm just testing out methods to get the result.

To be able to filter the form that has to be in the Datasheet view I added a subform so I could still see the comboboxes?

At this point I have tried using the Combobox FAQ that you directed me to:

Private Sub Combo0_AfterUpdate()
Combo2 = Null
Combo4 = Null

Combo2.RowSource = ""SELECT DISTINCT Test System # FROM TblMasterBlindList " & _
" WHERE UNIT #='" & Combo0 & "'"
Combo2.Requery

End Sub

Private Sub Combo2_AfterUpdate()
Combo4 = Null

Combo4.RowSource = ""SELECT DISTINCT Adjoining Component TS# FROM TblMasterBlindList " & _
" WHERE UNIT #='" & Combo0 & "' AND Test System #='" & Combo2 & "'"
Combo4.Requery

End Sub

When I select a number from the Unit # Combo0 nothing happens, then I click the second box Test System # Combo2 where I get this error:

The record source '"SELECT DISTINCT Test System # FROM TblMasterBlindList WHERE UNIT #='042' specified on this form or report does not exist.

At this point my comboboxes are unbound....and my subform can't link to them because they are unbound.

I have read so many different ways to do this at this point they are all running together and I can't get any of them to work....

 

[lespaul]

I'm guessing one of your issues is the spaces in your field name. You usually have to enclose field names that have spaces in brackets. Does this query work as it is in the SQL View?

SELECT DISTINCT Test System # FROM TblMasterBlindList WHERE UNIT #='SomeValue'

maybe:

SELECT DISTINCT [Test System #] FROM TblMasterBlindList WHERE [UNIT #]='SomeValue'

Additionally you need to have Combo0 filled with the data that you want to match to in Combo2. Where's the query that's filling Combo0?

Leslie