'Bound' Cascading Combos

Hi zubin6220,

If you have an example for unbound combos then all you have to do is set the control source for each combo to the field you want to store the value in.

However, you might feel you want to delete the current value in combo2 when you make a new selection in combo1, to avoid incorrect data being stored.
In that case, in the afterupdate event for combo1 you need to add:

me.combo2namehere=""

Pete

Hmm, basically you're having a nasty design problem with these three combo's.
In a normalized table only the "lowest" unique ID should be recorded as a foreignkey in the table and using cascading combo's will require to determine the "higher" values.
When you have all three combo's "bound" then you'll need to have an index defined and when a "higher" combo is changed, the lower values need to be forced to be empty.

What is exactly the "structure" you need for your combo's and do you want to have all three filds in your table or just the unique ID of "the lowest" ?

Nic;o)

The first combo is bound to state field
Second is bound to city field
Third is bound to unit field
There are three lookup tables for these fields
I was looking for a way to:  select state in cbo1, select city in cbo2, etc
I thought this was pretty 'standard' as far as simplifying data entry...maybe not?
    further...there are of course several cities listed for a certain states and units are specific to the cities.  
I was hoping that with all the sample db's floating around....one would certainly deal with this 'bound' cascading combo problem.  

Hmm, these three fields more or less "add up" to a unique Zip code. (Unit?)
The Zipcode is what I ment indicating a unique ID as "foreign key".
Normally recording a unique Unitcode will be sufficient to extract the needed unit/city/state.

Your combo's are more or less "filters" to get the user to select the proper Zip and the tables should hold the State, the State/City and the State/City/Unit information when normalized.

Is your Unit unique and how does your reference tables look ?

Nic;o)

Unit is unique.  All tables have id.

Example:

tlkpState
    StateID       pk
    StateName
tlkpCity
    CityID         pk
    CityName
    StateID      fk
tlkpUnit
    UnitID         pk
    UnitName
    CityID         fk
   

So we only need to record the UnitID in the "target table" of the form.
The Unit combo will have to be bound to this field, but the City and State are unbound.
"Problem" will be to set the City and State combo when a record is displayed with a filled UnitID.
This can be achieved by using the OnCurrent event to set the rowsource of the City and the value of the State.
Create a query joining State, City and Unit called qryStateCityUnit
Now use in the OnCurrent event:

dim rs as DAO.recordset
' get all data needed in rs
set rs = currentdb.openrecordset("select * from qryStateCityUnit where UnitID=" & Me.cmbUnitID)
' reset the rowsource
Me.cmbCity.rowsource = "Select CityID, CityName from tlkpCity where stateid=" & rs!StateID
Me.cmbCity.requery
' assign value City
Me.cmbCity = rs!City
' assign value State
Me.cmbState = rs!State

Getting the idea ?

Nic;o)

I'm still not understanding about the tables.  How will my selection for cmbState limit to those cities in the particular state

The AfterUpdate of the cmbState will have to fill the cmbCity's rowsource like:
Me.cmbCity.rowsource = "Select CityID, CityName from tlkpCity where stateid=" & rs!StateID

best also to set the cmbUnit to space as there might be an old value:
Me.cmbUnit = ""

and finally make it visible with:
Me.Refresh

Nic;o)

nico4038,
thanks.  when I get off vacation I will check it out and advise.

OK, keep me posted :-)

Nic;o)

Nico,

I'm still 'out' and can't get in to the db.  I will go ahead and close out this question.  I think you have cleared things up for me.  If I still have any trouble with this I will post and link to this.  Thanks for your patience and help.

No problem, you can always mail the link to me (see my profile) and I'll jump in :-)

Nic;o)