Cascading Combo Boxes - HELP PLEASE!!!!!!

[mikeinohio]

Hello I have 4 unbound combo boxes, and they are named:

CboPatCity
CboPatState
CboPatZipcode
CboPatCounty

I have a table Called CSZ which have the following fields:
CSZID = Autonumber - PK
City = Text
State = Text
Zipcode = Text
County = Text

and my after update event is coded as follows:


Private Sub CboPatCity_AfterUpdate()
Me!CboPatCounty = Null
Me!CboPatCounty .Requery
Me!CboPatCity = Null
Me!CboPatCity .Requery
Me!CboPatZipcode = Null
Me!CboPatZipcode.Requery
End Sub


and what i need to do is to cascade these combos boxes to allow the
user to first select a city, then then next combo needs to show me all
states this city may be in, then after selecting the state the user needs
to select a county, the the appropriate zipcode, allthis is necessary
sice biling is driven by the clients state,citym county and zipcode.

I have tried every example i found on this site but none are seem to be
working.

what is it i am doing wrong?

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Hello I have 4 unbound combo boxes, and they are named:

CboPatCity
CboPatState
CboPatZipcode
CboPatCounty

I have a table Called CSZ which have the following fields:
CSZID = Autonumber - PK
City = Text
State = Text
Zipcode = Text
County = Text

and my after update event is coded as follows:


Private Sub CboPatCity_AfterUpdate()
Me!CboPatCounty = Null
Me!CboPatCounty .Requery
Me!CboPatCity = Null
Me!CboPatCity .Requery
Me!CboPatZipcode = Null
Me!CboPatZipcode.Requery
End Sub


and what i need to do is to cascade these combos boxes to allow the
user to first select a city, then then next combo needs to show me all
states this city may be in, then after selecting the state the user needs
to select a county, the the appropriate zipcode, allthis is necessary
sice biling is driven by the clients state,citym county and zipcode.

See if this helps:
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[banem2]

Hello I have 4 unbound combo boxes, and they are named:

CboPatCity
CboPatState
CboPatZipcode
CboPatCounty

I have a table Called CSZ which have the following fields:
CSZID = Autonumber - PK
City = Text
State = Text
Zipcode = Text
County = Text

and my after update event is coded as follows:

Private Sub CboPatCity_AfterUpdate()
    Me!CboPatCounty = Null
    Me!CboPatCounty .Requery
    Me!CboPatCity = Null
    Me!CboPatCity .Requery
    Me!CboPatZipcode = Null
    Me!CboPatZipcode.Requery
End Sub

and what i need to do is to cascade these combos boxes to allow the
 user to first select a city, then then next combo needs to show me all
 states this city may be in, then after selecting the state the user needs
 to select a county, the the appropriate zipcode, allthis is necessary
 sice  biling is driven by the clients state,citym county and zipcode. 

I have tried every example i found on this site but none are seem to be
 working.

what is it i am doing wrong?

Assuming that you have 4 tables for each entity you need to connect
these for tables somehow, so you can know which city belongs to which
state, which zip code to which city, etc.

I will use simple example using 2 tables and 2 combo boxes.

tblCity:
IDCity, AutoNumber
IDState, Long Integer (Lookup Wizard and connect with IDState from
second table)
CityName, Text

tblState
IDState, AutoNumber
StateName, Text

On the form you need to place 2 combo boxes. First one is for
selecting State, unfiltered combo box with record source "tblState".

Next combo box needs to have filter by State. Instead table create SQL
string or use existing query. In query, on field tblCity.IDState put
filter like this:

Forms!myFormName!cmbState (filter by combo box State on form
myFormName)

After updating State combo box (cmbState) just execute cmbCity.Requery
to refresh second combo box source data.

For 4, or many more combo boxes, it is the same, as long as you have
field in table which you can use to connect with parent table.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[banem2]

Assuming that you have 4 tables for each entity you need to connect
these for tables somehow, so you can know which city belongs to which
state, which zip code to which city, etc.

I will use simple example using 2 tables and 2 combo boxes.

tblCity:
IDCity, AutoNumber
IDState, Long Integer (Lookup Wizard and connect with IDState from
second table)
CityName, Text

tblState
IDState, AutoNumber
StateName, Text

On the form you need to place 2 combo boxes. First one is for
selecting State, unfiltered combo box with record source "tblState".

Next combo box needs to have filter by State. Instead table create SQL
string or use existing query. In query, on field tblCity.IDState put
filter like this:

Forms!myFormName!cmbState (filter by combo box State on form
myFormName)

After updating State combo box (cmbState) just execute cmbCity.Requery
to refresh second combo box source data.

For 4, or many more combo boxes, it is the same, as long as you have
field in table which you can use to connect with parent table.

Regards,
Branislav Mihaljev
Microsoft Access MVP

There is one important thing I forgot to tell. Each combo box needs to
have at least 2 fields in record source: ID and Real Name.

In above sample, for cmbState use IDState and State name. For this and
each other combo boxes set the following properties:

ColumnCount: 2
ColumnWidths: 0; 1
BoundColumn: 1

As we are working on table level with tables ID we need to make combo
box read first data and that is ID field (IDState, IDCity, etc).

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[mikeinohio]

all the information is in one table called CSZ = city, State and zipcode,
and the fields in it are called:
city
state
county
zipcode

should i seperate this informationinto two tables?

 

[banem2]

all the information is in one table called CSZ = city, State and zipcode,
and the  fields in it are called:
city
state
county
zipcode

should i  seperate this informationinto two tables?

Not necessary. Depending on many things, sometimes "de-normalization"
of data is OK, but you need to be aware of disadvantages keeping all
data in one table (google for "rules of normalization" to see more
info).

What you need is to change RecordSource of 2nd, 3rd and 4th combo box
to include ID field and second field (County, City, ZIPCode) in case
where State is first combo box. Then just filter 2nd, 3rd and 4th
combo boxes by ID field. In query put filter on ID field like this:

= Forms!myFormName!cmbState

To repeat all. First combo box is State and in record source you have
query with fields ID, StateName. Set ColumnCount = 2, ColumnWidth = 0;
1 so you will see only StateName in combo box.

Use AfterUpdate event of cmbState and write this code:

cmbCounty.Requery
cmbCity.Requery
cmbZIPCode.Requery

In record source of each of these combo boxes use fields:

ID, County
ID, City
ID, ZIPCode
(filter on ID Field for each SQL: = Forms!myFormName!cmbState)

Set the same ColumnCount = 2, ColumnWidth = 0; 1 for each combo boxes.

Regards,
Branislav Mihaljev
Microsoft Access MVP