combo box with 6 columns from 3 cascading linked tables, returning values to 3 fields

[zoforos]

hi everyone,
I have a table for Buliding Permits. On the Building Permit's Data entry Form, I want a combo box to show 6 columns from 3 other cascading tables linked together...
AND put the values of 3 out of 6 columns to 3 fields inside the Building Permit's table. (1 of 3 values would be the "bound to" field for the combo box)
Did I scrambled your mind?
Let me explain.
I have a Basic table |tblBuildingPermits| with fields: [intKeyID], [intPermitNumber], [strOwner], [strCivilEng], [intCityID], [intStateID], [intCountryID].
Also...

• one table |tblCountry| with 2 fields: [intCountryID], [strCountry]

• one table |tblState| with 3 fields: [intStateID], [strState], intCountryID] (linked to tblCountry|)

• one table |tblCity| with 3 fields: [intCityID], [strCity], intStateID] (linked to tblState AND |tblBuildingPermits|)

Cascade Relations of |tblCountry| one2many |tblState| one2many |tblCity|
one2many|tblBuildingPermits|

I have made a combo box combo_City.
It's RecordSource a simple Query with 6 fields:[intCityID], [strCity], intStateID], [strState], intCountryID], [strCountry]
column bound 1
Bount to |tblBuildingPermits| [intCityID]
This Combo Box shows 6 columns, and when i choose a city the combo box returns the [intCityID] of |tblCity| to [intCityID] of |tblBuildingPermits|.

• I want the intStateID and intCountryID of my chosen city to update the corresponding fields on the tblBuildingPermits| at the same time of my choice. (Maybe in an after update event.)

• also, a locked textbox ctrState bound to [intStateID] of |tblBuildingPermits| but showing the [strState] of |tblState|

Thanks in advance...

 

[Galaxiom]

Your databse has normalization errors.

Each city is only in one state which is in one country. You should only record the cityID in the BuildingPermits table.