Auto fill text box by Combo box

[shuddle]

I know this is posted a lot but for some reason I can't seem to figure out what is wrong with my code.



Private Sub Location_AfterUpdate()
Me.LocState = Me.LocCity.Column(1)
End Sub


All I am trying to do is auto populate the state after the city has been entered.

The city combo box is based off a lookup table with the city and state as fields.



Am I missing a step somewhere?

[davegri]

The code should be in the after update event of the city combo box.

[Welshgasman]

You haven't updated that control though have you, you have updated the combo?
Are you saying you have a lookup field in the table for location?

[mike60smart]

Hi

You say:- "The city combo box is based off a lookup table with the city and state as fields."

The lookup table should be as follows:-
-tblStates
StateID - PK - Autonumber
State
City

Which makes this part wrong:-
Me.LocState = Me.LocCity.Column(1)

It should read:-
Me.LocState = Me.LocCity.Column(2)

[Gicu]

I think you should clarify the table structure for the the lookup table (city\state) and also for the actual data table where you store this location. Do you store both the city and the state in the data table? Have you considered storing just the foreign key (StateID in Mike's post #4 above)? If you do that then there is no need to have any code to display the city and the state on your data entry form, simply set the control sources of those textboxes to the appropriate combo column index and they will update automatically when you change the combo.

Cheers,

[shuddle]

Thanks for all the good insight.

I'm getting the error "Compile error: Method or data member not found" highlighting the .Column


My look up table: LookupTbl_City has
StateID as the primary key
City
State



LocCity lookup settings are

Row Source LookupTblCity
Bound Column 2
Column Count 3
Column Heads No
Column Widths 0;1;0.5

[mike60smart]

Row Source LookupTblCity
Bound Column 1
Column Count 3
Column Heads No
Column Widths 0;1;0.5

[shuddle]

It's resolved. It was a naming issue similar to what #2 Davegji pointed out. Thank you everyone for your help.

[Gicu]

@shuddle: the question I asked in post # 5 was if you want\need to save both City and State in your data table or just the CityState foreign key (StateID from your post 6) which would give you both city and state via a query or dLookup whenever needed. In that case you don't need any code in the after update event, just set the control source of the textbox where you want to display the state as [cboStateId].Column(2) (where cboStateId is the name of the combo bound to the field storing the StateID FK).

Cheers,