Field inherits values

[Guest]

I have a Supplier tracking form which is linked to a supplier table. I am
able to select which supplier the tracking item form is linked to, but I want
another field to update (automatically show) to show the selected suppliers
email address.

I want to do this so I can create a button on the tracking form get the
suppliers email address. Please help. I've created the tables, forms,
relationships, and code to email.

Thanks in advance

 

[Steve Schapel]

JR,

There are basically three approaches you can take to this...
1. Base your form on a query which includes your Suppliers table (I
assume this is where you have the email address for each supplier
stored), that way you have the email address immediately available in
the query, and hence you can represent it on the form with a bound
textbox. In this case, I would set the Locked property of the textbox
to No.
2. Place an unbound textbox on the form, with its control source set to
the equivalent of...
=DLookup("","Suppliers","[SupplierID]=" & [SupplierID])
3. If you are entering the Supplier on the form using a combobox, make
it a multi-column combobox, whose row source includes the email address,
let's say as the third column (after SupplierID and SupplierName).
Then, set the Control Source of the email textbox to the equivalent of...
=[SupplierID].[Column](2)

 

[Guest]

Use the dlookup function after you enter the supplier code
' If Supplier code is number
me.EmailAddressName = nz(Dlookup("EmailAddress","EmailTable","SuppCode = " &
me.SuppCodeNameInForm),"N/A")

' If Supplier code is Text
c = nz(Dlookup("EmailAddress","EmailTable","SuppCode = '" &
me.SuppCodeNameInForm & "'"),"N/A")

Assuming that you link the EmailAddress Table by supplier code.
You can put the dlookup (above) in three place
1. On the after update event of the supplier code in the form
2. On a click on the button on the form
3. Strait in the ControlSource Property of the email address field in the
form.
the line without the part "me.EmailAddressName"