[RESOLVED]MS Access form...need help with combo boxes

[noycwild]

Sorry for being a noob..I'm having a problem with my form. Ok here is the basic layout. I have 50 tables...All containing the same fields. Street,Town,State. Each table is labeled with a state name. I have a customer table also, which will make it 51 tables all together. Now the customer table has Telephone Number,Street,Town,State, and Date Submitted. On the form I want to have a text box for the telephone number which a user will type his phonenumber. the other three fields on the form will be 3 combo boxes,Street,Town,State. When the user clicks the combo box for the State, I want a list of all the States. The user will choose the state he/she wants. Now when the user clicks the Town, I want only the Towns available to that state which is in the table that has the state name to show up in that combo box. Now when they choose the town. Then they will be ready to choose the street. When combo box is clicked I only want to see names of streets in that town according to the state table. Lets say the customer has moved to a new location and they want to update there info. I need to beable to take the info from the form and add it to the customer table. I was wondering If someone could help me out in how to design this type of form...or if anyone could point me toan example of a form accessing data in this way. email:[email protected] if needed. Thanks guys for any help!

[DKenny]

Noycwild
You really need to rethink your database design. Having a table for each State is definetly not the way to go.
A much better structure would be to have the following structure
1/ a STATE table that contains a record for each state.
2/ a TOWN table that contains a record for every town and includes a FK relationship to the PK of the STATE table
3/ a STREET table that contains a record for every street and includes a FK relationship to the PK of the TOWN table.

With this structure you can populate your State combo from the STATE table. When the user selects a State, you can pass the value of that combobox to a SQL statement that poplates the Town combo. Repeat this process to populate the Street combo based on the value selected in the Town combo.

[noycwild]

Noycwild
You really need to rethink your database design. Having a table for each State is definetly not the way to go.
A much better structure would be to have the following structure
1/ a STATE table that contains a record for each state.
2/ a TOWN table that contains a record for every town and includes a FK relationship to the PK of the STATE table
3/ a STREET table that contains a record for every street and includes a FK relationship to the PK of the TOWN table.

With this structure you can populate your State combo from the STATE table. When the user selects a State, you can pass the value of that combobox to a SQL statement that poplates the Town combo. Repeat this process to populate the Street combo based on the value selected in the Town combo.

Thanks...I appreciate that...it will make a lot of things easier...I also don't think access is the best idea for this because of how much data will be accessed by multiple users.. My boss just wanted me to come up with some prototype form we will probably use SQL or Oracle.