Question about drop down list and tables

[dave@homedeliverygroup]

Hello everyone,

On my form I have two drop down lists "Markets" and "Store # and Location"...

I have a question on my form the user selects data from a drop down list
that contains the state abbrevations for our several hubs(Markets) ex: DE,
PA, NJ, MA, ATL and in those hubs have about 30 stores each that we service.
Each store has its own table in the database consisting of the stores for
that market... now is there a way that when the end user selects the Market
from the Market drop down list I can have the "Store# and Location" drop down
list only use the table from that market? Maybe with a "if else"
statement????? So if the user selects the DE market in the the dropdown list
for the "Store# and Location" will only contain the stores for the DE
market???

 

[Access101]

I can't say for sure, because I don't understand your design criteria, but I
would first combine all the Store tables into a single table.

Can you help me understand why each store needs a separate table?

 

[Klatuu]

This is a common question. The solution is known as "Cascading Combos"
What you do is put a Where clause on the Store combo's row source query to
filter it to only those stores in the selected market.
Then in the After Update event of the market combo, you requery the store
combo.

The store's query would be something like:

SELECT StoreID, StoreName WHERE MarketID = Me.cboMarket;

Then the query in the market combo:

Me.cboStore.Requery

 

[dave@homedeliverygroup]

Access101 i have the stores broke down because i did not want to have a drop
down list with 210 stores in the table i though it would be easier to
view/select from a list of 30 stores intead of 210 stores... that is why i
wanted to break them down by market and it was not nessesary for the other
markets to see the markets stores...

 

[Klatuu]

Still not a good design.
The correct way would be to have field to identify the market. Use
cascading combos to first select a market. By filtering the row source of
the store combo on the value in the market combo, you will see only the
stores for the selected market.

 

[dave@homedeliverygroup]

Klatuu...

Thanks i have tried what you said... i don't know i can't get it... it just
does not work... seems like this should have been a simple process it is not
a complex DB very simple and straight forward... i don't know
--
thanks for your help

Dave

This is a common question. The solution is known as "Cascading Combos"
What you do is put a Where clause on the Store combo's row source query to
filter it to only those stores in the selected market.
Then in the After Update event of the market combo, you requery the store
combo.

The store's query would be something like:

SELECT StoreID, StoreName WHERE MarketID = Me.cboMarket;

Then the query in the market combo:

Me.cboStore.Requery