Lookup (vlookup) field to another table's

[Hueyv]

Sorry if this question do not belong in this thread/discussion. I have a challenge where I need to have a field lookup the record to another table's field. This involves 4 tables. The table design/scenario is this:
Tables:
1. Customer (Customer)
2. Tool (Tool revision)
3. Quote (Quotation)
4. Sales (containing salesperson)

Relationship:
Customer -> one-to-many -> Tool
Sales -> one-to-many -> Quote
Sales _> one-to-many -> customer

In the table/form - though a field lookup or reference:
A salesperson enter a new quote and choose a customer. As soon as he/she chooses the customer, the 'tool' field displays (combo/list) the tool revision for that particular customer only.



I am having difficulty when he/she choose the customer, every 'Tool' for ALL customer displays.

How do I solve this problem?

Much appreciated it.

Hue

[ranman256]

the combo sql for tools should be referencing the customer.
the sql would be:

select tool from table where [CustID] = forms!frmMyForm!cboCustomer

[Hueyv]

Thank you ranman256! I will give that a try and let you know.