Retrieve data from other table

  • Thread starter Thread starter Herman Wellington
  • Start date Start date
H

Herman Wellington

When I input data in a form, say staff code. I want to retrieve the staff
name from other table. Should I do it in Event?

Thanks a lot.
 
Herman Wellington <Herman
When I input data in a form, say staff code. I want to retrieve the staff
name from other table. Should I do it in Event?
Click to expand...


There are lots of ways to do that. In most simple
situations, you can use the staff code text box's
AfterUpdate event procedure:

Me.staffname = DLookup("staffname", "othertable", _
"[staff code]=" & Me.staffcode)

Replace all those names with the real names.

That will not produce a desirable result in a continuous
form. In this case you can modify the form's record source
to be a query that joins the two tables so the name is
included in the form's field list. This approach will not
display the name in new record until the record is saved and
the form is requeried (which brings along other issues).

Except for a potentially serious speed degradation, you can
avoid the code altogether by doing it all in the name text
box's expression:
=DLookup("staffname", "othertable", "[staff code]=" &
staffcode)
 
Thanks Marshall,

It looks a little complicated from what I expected. Will try and see.
Thanks again.

Herman


Marshall Barton said:
Herman Wellington <Herman
When I input data in a form, say staff code. I want to retrieve the staff
name from other table. Should I do it in Event?
Click to expand...


There are lots of ways to do that. In most simple
situations, you can use the staff code text box's
AfterUpdate event procedure:

Me.staffname = DLookup("staffname", "othertable", _
"[staff code]=" & Me.staffcode)

Replace all those names with the real names.

That will not produce a desirable result in a continuous
form. In this case you can modify the form's record source
to be a query that joins the two tables so the name is
included in the form's field list. This approach will not
display the name in new record until the record is saved and
the form is requeried (which brings along other issues).

Except for a potentially serious speed degradation, you can
avoid the code altogether by doing it all in the name text
box's expression:
=DLookup("staffname", "othertable", "[staff code]=" &
staffcode)
Click to expand...
 
Back
Top