autolookup for a form - need help

[Guest]

I have a table (table1) that has a field that is filled in. It is "zipcode".
There is another field called "city". This is the table we use to fill in new
records.

In another table (table2) I have the fields "zipcode" and "city" as well.
Both of these fields are already filled in. Thus for each zipcode, the
corresponding city exists in this table.

What I want to do is have the field "city" in table1 fill in automatically
when I input the "zipcode". Table1 somehow has to lookup the information in
table2 and then put the city into the "city" field by matching the zipcode.
What is best way to do this?

Thanks for your help.

Bryan

 

[Al Camp]

Bryan,
Use a comnbo box to select the zipcode. Make it a 2 column combo, with
City in the second column. When the user selects, or enters a legitimate
Zipcode, use the AfterUpdate event of that combo to update the City field
with the associated City in the second column.
Private Sub cboZipcode After_Update()
Me.[City] = cboZipcode.Column(1)
End Sub
(Combo columns are numbered 0,1,2,3, etc.. so the second column is 1)

 

[Guest]

Bryan,

Just an add on the Al Camp's input.

Create a form with Record Source "Table2" and insert both the fields in the
form with respective Control Source.

Ensure that the Zipcode field type is Combo Type. Insert the following in
the Record Source of the ZipCode:
SELECT Table1.ZipCode, Table1.City FROM Table1 ORDER BY Table1.ZipCode;

Change Column Width of the Zipcode to "1;0" (with quotations).

Insert the following in the After Update event:
Me!City = Me!ZipCode.Column(1)

Run the form. You will get the result.

 

[Guest]

Bryan,

Sorry, there is a typo error:

Change Column Width of the Zipcode to "1;0" (without quotations).

 

[Guest]

So, just to make sure I have this right, the data always gets entered into
the form, not the table. The tables hold the data, but users do not enter
the data directly into the table, especially in this type of autoentry?

Thanks to both of you for your help.

 

[Guest]

Bryan,

That depends how you design the form. The example enters data direct to the
table. Otherwise, you must use RecordSet and variables. You must assign the
data input to each variable. Then, you assign the variable to each Recordset
(table fields) and update them into the table.