Perplexing listbox challenge

[Ed1234567]

HI! I have a subform that has the following listboxes:LstStateSource,
LstStateDest,LstContySource,LstCountyDest,LstCitySource,LstCityDest,
LstZipSource,LstZipDest. All the "Lst*Source Listboxes are connected to a
zipcode table. The Lst*Dest boxes displays the table info for that record.
Example:TblCity,TableState etc... Anyway, What I would like to do is if the
user picks a state in lstStateSource, then all the cities,counties and
zipcodes associated with that state are saved to the tables. The same if
county is selected in LstCountySurce Listbox.It would then save all the
cities and zipcodes associated with that county in the proper tables.
LstCitySource would add all the zipcodes related to that city.Any help on
solving this puzzle will be appreciated! Thanks! I hope I explained it so it
not confusing!

 

[TonyT]

Hi Ed,

Not sure what you are trying to acheive here or why, as it sounds like you
may end up with many duplicates of the same data in many tables, not good
practice, but, if you look into having something like;
INSERT INTO tblDestCounty (County Field) SELECT * FROM tblSourceState WHERE
me.lstSourceState.selected = tblSourceState.State
If you only want to display the data in your subform listboxes, not enter it
into tables as you say then use SELECT instead of INSERT INTO and set this as
the rowsource for the listbox in your subform from the after_update event of
the main forms listbox as described above.

as a side note a combobox would seem more a appropriate way to *prompt* the
user for a single choice of state/county etc etc.

TonyT..

 

[Ed1234567 via AccessMonster.com]

Hi Tony,
Thanks for the response! What I am trying to accomplish is this: We have this
database that has vendors throughout the US. I have a search form that
searches by states servered,counties served,cities served,zipcodes served.
The thing is that the piece for entering this information needs to be easy on
the user doing the data entry. If only the state served is entered for the
vendor,then, if we searched on zipcodes served they would not show up. Or if
we searched in city served, it would not show up even though they serve the
whole state. So all the cities,counties and zipcodes have to be associated
with that vendor. It is even more of a nightmare if the company serves a
nationional regegion! All the cities,counties and zips need to be associated
to them! I have the followeing tables: Vendor(has general info) VendorState,
vendorCounty,VendorCity,VendorZip. The dadtabase is normalized. What I was
thinking was having listboxes that are linked to the national zipcode table
that the user could multiselect state or state and it would put the counties,
cities and zipcodes relating to that state (or States)into the proper tables.
The county and city listboxes would work the same way. Then(whew) the
information that is placed in those tables would be displayed in
corresponding listboxes on the subform associated with the vendor. I atried
using sql statemernt to link tables in vb but got the error message that the
data is not updatable. Any Ideas will help! Thanks for your intrest!

Hi Ed,

Not sure what you are trying to acheive here or why, as it sounds like you
may end up with many duplicates of the same data in many tables, not good
practice, but, if you look into having something like;
INSERT INTO tblDestCounty (County Field) SELECT * FROM tblSourceState WHERE
me.lstSourceState.selected = tblSourceState.State
If you only want to display the data in your subform listboxes, not enter it
into tables as you say then use SELECT instead of INSERT INTO and set this as
the rowsource for the listbox in your subform from the after_update event of
the main forms listbox as described above.

as a side note a combobox would seem more a appropriate way to *prompt* the
user for a single choice of state/county etc etc.

TonyT..

HI! I have a subform that has the following listboxes:LstStateSource,
LstStateDest,LstContySource,LstCountyDest,LstCitySource,LstCityDest,

[quoted text clipped - 8 lines]

solving this puzzle will be appreciated! Thanks! I hope I explained it so it
not confusing!

 

[TonyT]

So if your database is normalised you will have links between the tables to
identify parent and child records, cascading down from state to county to
cities to zip. If you create 1 query drawing all this information together
you need only find out if the user is entering a state, county, city or zip
(combobox with value list) and then a 2nd combobox which selects the relevant
data from the query into which the user enters the name of the state, county
etc. if it's covered it will show eg;

After_update cboType () 'cbotype = name of 1st combobox
dim strSQL as String
Select Case cboType
Case "State"
strSQL = "SELECT DISTINCT qryState FROM qryCovered"
Case "City"
strSQL = "SELECT DISTINCT qryCuty FROM qryCovered"
etc etc etc

me.cboSelected.rowSource = strSQL
End Sub

TonyT..

Hi Tony,
Thanks for the response! What I am trying to accomplish is this: We have this
database that has vendors throughout the US. I have a search form that
searches by states servered,counties served,cities served,zipcodes served.
The thing is that the piece for entering this information needs to be easy on
the user doing the data entry. If only the state served is entered for the
vendor,then, if we searched on zipcodes served they would not show up. Or if
we searched in city served, it would not show up even though they serve the
whole state. So all the cities,counties and zipcodes have to be associated
with that vendor. It is even more of a nightmare if the company serves a
nationional regegion! All the cities,counties and zips need to be associated
to them! I have the followeing tables: Vendor(has general info) VendorState,
vendorCounty,VendorCity,VendorZip. The dadtabase is normalized. What I was
thinking was having listboxes that are linked to the national zipcode table
that the user could multiselect state or state and it would put the counties,
cities and zipcodes relating to that state (or States)into the proper tables.
The county and city listboxes would work the same way. Then(whew) the
information that is placed in those tables would be displayed in
corresponding listboxes on the subform associated with the vendor. I atried
using sql statemernt to link tables in vb but got the error message that the
data is not updatable. Any Ideas will help! Thanks for your intrest!

Hi Ed,

Not sure what you are trying to acheive here or why, as it sounds like you
may end up with many duplicates of the same data in many tables, not good
practice, but, if you look into having something like;
INSERT INTO tblDestCounty (County Field) SELECT * FROM tblSourceState WHERE
me.lstSourceState.selected = tblSourceState.State
If you only want to display the data in your subform listboxes, not enter it
into tables as you say then use SELECT instead of INSERT INTO and set this as
the rowsource for the listbox in your subform from the after_update event of
the main forms listbox as described above.

as a side note a combobox would seem more a appropriate way to *prompt* the
user for a single choice of state/county etc etc.

TonyT..

HI! I have a subform that has the following listboxes:LstStateSource,
LstStateDest,LstContySource,LstCountyDest,LstCitySource,LstCityDest,

[quoted text clipped - 8 lines]

solving this puzzle will be appreciated! Thanks! I hope I explained it so it
not confusing!