Text Box With Row Source from Another Table

[Guest]

I currently have the following SQL in the Row Source for a list box. The goal
is to find out the name of the employer bsaed on the customer's name
(ingzCustNo in table tblEvaluation) when it is selected in a certain form.

I have three tables: tblEmployer that holds my company information,
tblContacts that holds the contacts' information, and tblEvaluation that
holds the evaluations' information. On the form for which tblEvalution is
based, I'm able to write SQL to pull values from tblContacts because
tblContacts is joined via relationship to tblEvaluation. Because
tblEvaluation isn't directly joined to tblEmployer, my SQL doesn't work. How
can I pull the name of the company that the contact works for into my
Evaluation form?


SELECT tblEmployer.chrER FROM tblEmployer WHERE
tblEvaluation.ingzCustNo=tblContacts.atnCustNo AND
tblEmployer.atnERNo=tbContacts.ingzEmployer;

 

[Guest]

Hi Jennifer

A simple solution would be to base the form on a query and include all 3
tables tblEmployer / tblContacts / tblEvaluation in the query. You would
then be able to "bring-in" the field onto the form. You need to ensure that
the jois in the query design grid are such that will allow this but it is not
too difficult and should enable you to get the results you want.