linking fields

  • Thread starter Thread starter sonta
  • Start date Start date
S

sonta

I have a form ( frmInv )that has various fields and the data source for this
form comes from a table ( Tbl A) . Two fields that are present in this form
are Suburb (combo box) and Postcode (text box).

I have another table (Tbl B) which has the following 2 fields only:
Suburb
Postcode
They have pre existing data of existing suburbs and the corresponding
postcodes. Naturally this data stays as is ( no additions required by user).

It is from Tbl B that the suburb field (combo box) in the form gets its
source data list of suburbs from, which to choose from in the drop down box.

Question: I would like for a user to choose a suburb from the combo drop
down box in the form and upon pressing the ENTER button - to move on to the
next field which is the Postcode field. It is here that I would like the
database to automatically present the postcode number corresponding to the
chosen suburb in the suburb drop down box previously.

How do I do this ? macro or code expression ? if so can someone help me
out with the macro or code expresssion . thanks.
 
Thank for replying.
I tried both ways that you suggested but got an expression error message.
It related to the part of the code:
"Me![Suburb]=TblB![Suburb]")

I forgot to mention that there already was a primary key in the database. It
is a field called CaseID in Tbl A.
Based on this, would i simply insert a CaseID field in Tbl B as well? then
link the two tables together ?

thanks again.

Corrina said:
Technically, you really should have a primary key in Tbl B that as a
Click to expand...
linked field in Tbl A replaces the Suburb and Postcode fields. This way
your Suburb and Postcodes will always be consistant (avoiding typos) and
less information will need to be stored.
Sticking with how you have it, you would want to use the After Update
Click to expand...
event for the Suburb combo box. With the Postcode as a text box, you could
use:[Postcode]=DLookup("[TblB]![Postcode]","[TblB]","Me![Suburb]=TblB![Suburb]")
--(if this doesn't work, change Me![Suburb] to Forms![frmInv]![Suburb] -
sometimes this reference is picky)
There are some other options possible by changing the Postcode to a combo
Click to expand...
box, but I don't want to confuse you.
 
Back
Top