Limiting lists based on prior field selection

[DAWNY2007]

Ok. I am a little rusty with Access and I have been wracking my brain on this one. I have created a form, where I am trying to have the user select STATE...then based on STATE selected, I want to limit the list to the Counties in that state. Based on that combination, I want the limited list of Zip Codes for each State / County. I have created 3 tables and tried to create a query linked by ID....However, there are duplicate values on state (due to multiple counties / zip codes). My head hurts...any ideas ?

[ranman256]

the query for combo cboCounty is based on cboState
qsCounties: select [county] from tCounties where [State]=forms!fMyForm!cboState

and only code you need is to refresh cboCounty when user changes cboState:

sub cboState_afterupdate
cboCounty.requery
end sub

[DAWNY2007]

So it would have to be written in code ? I can't have drop-downs in the form that get limited based on the State , then County selections ?

[Micron]

You need code for cascading combos to work. Each time you pick a parent value the child combos have to be requeried, using the parent combo value as the criteria for the child combo list. They don't do this on their own.

[mike60smart]

Can you upload your database?

[DAWNY2007]

I tried to attach a dummy version of it (proprietary information), but no matter what I do, the database is too large to attach.

[DAWNY2007]

There is limited data (excluding the state / county / zip tables, as this us proprietary information

[mike60smart]

Hi

You will need to step back a bit , stop what your are doing and read up on Normalisation of Tables.

None of your tables have an Autonumber as the Primary Key. Each table should have an Autonumber.

You are using Lookup fields in tables. This is a No - No in Access

Your fieldnames should NOT have any Spaces or any characters other than A through to Z

You should set Referential Integrity between related tables.

[DAWNY2007]

Thank you. I will look into this. I do have 3 tables with autonumbers. I should probably save the look-ups for the form design and remove from the table itself. Users will only be touching the form. As I said, I am a bit rusty ! Thanks so much. I will start from the beginning :-)

[davegri]

You can do all that from one table.



Here's example DB:

Dawny-davegri-v01.zip

[DAWNY2007]

Thanks for this, however, the users will not know the zip code. So, I am looking for zip code to automatically populate based on the state / county combination. Note that I am aware that there are several zip codes per county, but for our purposes, we are limiting to one per county, as we just need general area. Thanks !

[davegri]

Thanks for this, however, the users will not know the zip code. So, I am looking for zip code to automatically populate based on the state / county combination. Note that I am aware that there are several zip codes per county, but for our purposes, we are limiting to one per county, as we just need general area. Thanks !

OK easy enough. Changed combo to text box. Text box shows a random zipcode in the state/county.



DB: Dawny-davegri-v02.zip

[DAWNY2007]

Thank you !!!!

[DAWNY2007]

In looking at this now, this is great for a simple look-up. However, for my needs, I need the data for State / County and Zip to be stored in the table for each record. I am assuming that I can select a control source. I'm going to play with this a bit.

[DAWNY2007]

I'll admit that I copied and modified this to match my table / field names. And I think I got the control source thing figured out. However, if you see attached, I am getting an error after selecting STATE / COUNTY. Getting closer !