Data from a Different table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!
In a form how can you auto fill a field from a value of
different table. I have the postal table for city, state
and zip. In my form for another table, I want to autfill
the city and state (from the Postal table) when I type in
the Zip code.

Thank you.
 
This is just what a relational database is designed to do. You have your
Town/City/State or Zip Code table which contains an Autonumber field, ZipID,
the Zip code and the Town.
In your address list table you have the other address fields (not
Town/City/State or Zip Code) and ZipID, a number field linked from the Code
And Town table.

Base a query on your Address List table, put all the fields in the grid. Add
the Town table to the grid and put all the fields EXCEPT the ZipID and Zip
Code into the query. Base your form on this query. In form design view, add
a combo box based on the Zip Code table, containing ZipID and Zip Code. Let
the wizard hide the ZipID field. 'Store the Value' in ZipID. When you choose
a zip code from the list, the other fields will automatically be filled in.
Evi
 
One way would be to have the use click a button when they are finished with
the zip code. In the OnClick event of the button you could use something
like this.

Me.txtCity = DLookup("City", "PostalTable", "ZipCode=" & Me.txtZipCode)
Me.txtState = DLookup("State", "PostalTable", "ZipCode=" & Me.txtZipCode)

This will allow the user to change the City and State if they are wrong. The
state usually isn't wrong, but the city is frequently the city of the post
office that delivers the mail, not the city that the client actually lives
in.
 
Back
Top