Hello all.

Firstly, my apologies if this post is out of place - I'm a newbie and not sure where this should go.

I would like to know whether it is possible to use a field from a table as a variable in Access?

To provide some detail, I am using a table to store data in regard to documents. One field records the organisation who received the documentation and another records the specific contact person in the organisation. The organisation lookup links to a seperate organisation table and the contact person links to a seperate contact person table.

The organisation is selected first. I want to limit the possible contact person options that can be chosen on the basis of the organisation that is chosed.

I.e. - I want to use a field value (organisation) as the criteria for a lookup in the contact person table. I can achieve this using a static value (i.e. If I type in the an organisation name, the lookup for contact person returns the correct options). However, I cannot get the lookup to use the value in the field as a variable.

Would it be possible for someone to let me know if this is (a) possible and (b) if so, what is the syntax that should be used in either the criteria space (if using the SQL query builder) or the direct SQL syntax?

Any assistance would be much appreciated.

Many thanks.

Kevin

Your best bet here is cascading combo boxes, so that the selection in the organization box is used as criteria by the row source of the contacts box. Candace Tripp's web site has an example.

Candace Tripp