Cascading Combo Boxes (1 Viewer)

[vowelsounds]

I can't figure out how to create the cascading combo boxes that I need.
For this, I have three tables:
Locations -- All store locations
Products -- Part Number and DESCRIPTION of any type of item that is available for rent
Serial Numbers -- (Unique) Serial number for each individual product, with its Product ID (foreign key) and Location ID (foreign key)

Each location has its own set of products available for rent, each with their own serial number. There may be more than one of the same TYPE of product at a location, but there will never be a repeated serial number.

I would like to have three (cascading) combo boxes. The first would allow the user to select a LOCATION. The second will have a list of all the types of products available at that location (DESCRIPTION). The third should have the list of SERIAL NUMBERS available at that LOCATION for that type of product (DESCRIPTION).

I hope this makes sense. I've tried using an after update event on the location box based on a guide I found online (I can't link) but I couldn't get it to work...

Any help on this would be greatly appreciated!

 

[MarkK]

but I couldn't get it to work...

Can you give specifics about what's working, what isn't, and what you've tried. Otherwise there is about 97% chance that my guess about what you need to know will be wrong, AND, this topic is already covered extensively by many other threads.

Thanks for your understanding,

 

[vowelsounds]

Yes, sorry!
I tried doing an After Update on the combobox that lists the locations (Combo0):

Me.Combo2.RowSource = "SELECT Description" & _
"FROM Query3 " & _
"WHERE LocationName = " & Nz(Me.Combo0)

I have a query (Query3) that lists every serial number with its location and description. (This was just to begin... I thought if I could get anything to work at all, it would be a start.) It kept throwing up a syntax error. I tried a bunch of other combinations(?), and there would either be an error or, at best, the dropdown would be blank.

I then tried to put what was selected in Combo0 as criteria in Query3, then put the row source of Combo2 as Query3.Description. This works... to an extent. It does show all the descriptions at that location, but it's for every single serial number (so there are very many repeats), AND I have to figure out how to get it to requery(?) for when I change the location selected in Combo0.

This is the guide:

fmsinc DOT com/microsoftaccess/forms/combo-boxes/cascading DOT html

 

[JHB]

I suppose the criteria for the location name is text, if yes you need to put in between single quotes, also missing spaces between "Description" and "FROM" could sometimes cause problem.
So try the below

Me.Combo2.RowSource = "SELECT Descriptio[B][COLOR=Red]n "[/COLOR][/B] & _
                           "FROM Query3 " & _
                           "WHERE LocationName = [COLOR=Red][B]'" [/B][/COLOR]& Nz(Me.Combo0) [B][COLOR=Red]& "'"[/COLOR][/B]