Trying to Populate from a table with input from another field on form

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I'm pretty much an amateur access guy. So I'd greatly
appreciate any guidance anyone can offer.

My question: I'm trying to display a value in a field
(Descriptor) from a 2nd table (tab-merchant). The tricky
part for me is that I want access to pull this
information from the 2nd table when data is input to the
form from another field.

The 2nd table is layed out with two fields (MerchantNbr &
MerchDescriptor).

The form is all built off one master table which includes
the data input for MerchantNbr. Can I have access lookup
the MerchDescriptor in the 2nd table and display that on
my form?
 
Jody,

Make a query based on the two tables, joined on the MerchantNbr field
from each. Place the fields from Table1 that you want on the form,
plus the MerchDescriptor field from Table2. Base your form on this
query, rather than on Table1. Have the controlsource of the Desciptor
textbox on the form to be the MerchDescriptor field, (and set it's
Enabled property to No and its Locked property to Yes so noone can
edit it on this form!) So now, when you enter the MerchantNbr is
entered on the form (you might consider using a combobox for this),
the MerchDescriptor will automatically be shown.

Here's another method. If you use a combobox to enter the
MerchantNbr, the RowSource of the combobox will be Table2. Make it a
2 column combobox. Then, add an unbound textbox to the form for the
Descriptor, and in its Controlsource put
=[NameOfYourCombobox].[Column](1)

Reading between the lines of your post, I am wondering whether you
actually have a Descriptor field in Table1. If so, this is not a
valid approach... the MerchantNbr field in Table1 should always refer
you to the MerchDescriptor field in Table2.

- Steve Schapel, Microsoft Access MVP
 
Back
Top