Fill in textboxes according to the value in a combobox

[lastout]

I hope I can describe this clearly. I have a form for adding new records to tblProject. Each new project record has an address that includes city, state, zip, county, borough, etc. I have created a zipcode table with every possible zip code in the area. What I want to happen is, when the user is adding a new project, he or she chooses a zip code from the list in cmbZip and the fields city, state, etc., are filled in accordingly AND the values are saved to the underlying project table. If the city, state, etc. controls are textboxes, not combo or listboxes, how can I cause their values, based on the zip code selected, to fill in on screen and save to the underlying table when their values are being pulled from tblZip, not tblProject? Confusion. Thanks for any help anyone can provide!

lastout (the game's not over till it's over)

 

[scking]

The RowSource for the combobox containing the zip code should be based on a query. The query will contain the zip, city, state, etc. You then modify the following properties for the combobox.

Columns will contain the number of fields in the query
ColumnWidths will contain the width of each.
So any column to be displayed will have a width
> 0

When the user clicks on the combobox and selects a zip code the box then includes x number of columns either displayed or not.

SELECT zip, city, state FROM tblZip;

would have three columns and the zip only being displayed. In the AfterUpdate event you merely assign the values of the combobox columns to the appropriate text boxes.

Sub cboZip_AfterUpdate

Me.txtZip = cboZip.Column(0)
Me.txtCity = cboZip.Column(1)
Me.txtState = cboZip.Column(3)

End Sub

Steve King ----------------------
Steve King
mailto:scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[lastout]

Thanks Steve. I did as you suggested but I am getting an error message "Type mismatch" with the debugger highlighting the first line: Me.txtCity = cboZip.Column(1). Can you tell me why this is happening and what to do about it? Much appreciated! lastout (the game's not over till it's over)

 

[scking]

The table you use to fill your combo box and the table where you are loading your data must be typed the same. For instance:

vtblZipCodeStuff
Zip Text

tblOtherTable
Zip Numeric Double

This is an extremely simple example but should be enough for you to do a side-by-side verification of the two tables to verify the data types. ----------------------
Steve King
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------