Tables/Lookup (1 Viewer)

[LadyDiss]

I am trying to create a database with for clients that have a first name last name field and also they will have a state and location field ... however, I want it to lookup, if I pick a state, only show me the locations available in that state (there are over 100 locations for 3 states) ... how would I do this? I want the State as a drop down and the locations?

 

[Alexandre]

I believe that what you are after are cascading combos based on two tables (Tbl_States and Tbl_Locations) with a one (Tbl_States) to many (Tbl_Locations) realtionship.
If you do a search on 'cascading combo' you will find many threads indicating how to set this up.

 

[LadyDiss]

I've tried and tried to set up the cascading combo, but obviously keep doing something wrong. Do I need a DatasheetResults type table to tie everything together? Or can I just skip that and allow it to do it in the form? What is the most efficient way? thanks.

 

[Alexandre]

Since you did not provide details about the strucutre of your db I am assuming that you have 3 tables at least:

Tbl_Customers
- CustomerID (autonumber PK)
- CustomerLocationID (foreign long integer key from Tbl_Locations
)
- OtherDetailFields...

Tbl_States
- StateID (autonumber PK)
- StateName (string)


Tbl_Locations
- Location ID (autonumber PK)
- StateID (foreign long integer key from Tbl_Locations
- LocationName (string)

Relationships:
Tbl_States: 1___Many: Tbl_Locations : 1___Many: Tbl_Customers


Your form is supposedly based on Tbl_Customers
Your first combo is unbound and uses Tbl_States as recordsource
Your second combo is bound to the CustomerLocationID filed in Tbl_Customers and uses a query based on Tbl_Locations
as recordsource with a where clause referring to
Forms!YourFormName!YourComboStateName

In the after update event of Your 'State' combo you requery the 'Location' combo. You also do that from the OnCurrent event of your form.