Linking two fields in a form

[SunGodly]

Hi, I am a new developer and have what I think should be a simple task but I can't quite figure it out. I have a simple customer database, where each customer is assigned to a specific location through a combo box (locations are looked up in a table). What I want to do is automatically enter the city and state in two other fields based on the location selection. Can anyone give this newbie a hand?
Thanks in advance!

 

[FancyPrairie]

Assume your combo box is composed of 3 fields; location, city, state. Where the width of city and state are 0 (so they are not shown as part of the drop down). The controlSource property of the 2 text boxes should be set as follows:

City TextBox:
ControlSource ... =YourComboBox.Column(1) (city)

State TextBox:
ControlSource ... =YourComboBox.Column(2) (State)

Now, when the users selects a location from the combobox, the city and state fields will be updated automatically.

 

[lhite]

The way that I would approach this would be to have the data source of the combo box contain the city and state (even if they are not displayed in the combo box). You don't need to populate any fields in the data table with the city and state unless a given location could have more than one city/state. (If that's the case you might find it easier to assign location codes instead of a description of the location).

You could then make a query to join the customer table with the location table so that the City/State will be displayed automatically (from the joined Location table). Use this query as the data source for your form.

-Larry

 

[SunGodly]

Thank you both for your help - I am giving stars as I imagine this is a common question for beginners and your answers were concise and much appreciated!

 

[educate889]

Thanks for the above info Fancy. . .

But I tried this and got the text box to populate with the value of the combo box, but it did not save in the table.

How can the value "city" be saved in the table if you use control source = combo .. .

City TextBox:
ControlSource ... =YourComboBox.Column(1) (city)

I am not sure I am making this clear but I am missing something and would appreciate a tip.

 

[SBendBuckeye]

See this thread as well. It has examples in it.

thread702-437460

Also, if you have a fairly extensive programming background, spend the money to get The AccessYourVersion Developers Handbook by Getz, Litwin, etc. It will be worth its weight in gold to you. NOTE: If you are a programming newbie, don't let it blow you away. It is an excellent resource. Also don't forget your local public library as a resource. When I first got started, I checked several very good books out to get started.

Good LucK!

 

[FancyPrairie]

Sorry, didn't realize that you wanted to save the City and State. Therefore, the method I suggested wouldn't work. However, the following will.

The ControlSource Property of the city text box should be set to the corresponding field in your table. Likewise for the state.

In the AfterUpdate event of the combobox, set the City and State fields accordingly.

In the example below, cboLocation is the name of your combobox, txtCity is the name of the text box that is bound to the City and txtState is the name of the text box that is bound to the state field.

Private Sub cboLocation_AfterUpdate()

If (Not IsNull(cboLocation)) then
txtCity = cboLocation.Column(1)
txtState = cboLocation.Column(2)
else
txtCity = null
txtState = null
End If

End Sub

By the way, as someone suggested, if you are saving the location in your table, you don't need to also save the city and state. Since the location implies a given city and state.