Using lookup to limit another lookup

[Desiree Keener]

I am trying to use the value from one lookup field to
limit the records available for another lookup field.

For instance, I use the lookup field to assign a company
name to a record. I then would like to lookup the contact
at the company. I have assigned this field as a lookup
field, but I would like to limit the records to be only
those contacts for the associated company.

I can get this to work once, but in order to get the
lookup for the contact refreshed, I have to close the
table & reopen it. Is there any way to get the lookup
query to rerun each time I select a new record?

Thanks for any help with this.

 

[Nikos Yannacopoulos]

Desiree,

Make a form for entering new records to the table. On the
form you can use the On Change event of the first combo to
run a macro or code to Requery the second. To the best of
my knowledge, this cannot be done on a table.

HTH,
Nikos

 

[Tim Ferguson]

For instance, I use the lookup field to assign a company
name to a record. I then would like to lookup the contact
at the company. I have assigned this field as a lookup
field, but I would like to limit the records to be only
those contacts for the associated company.

It's a design problem. You don't want to store Records.Company because the
company is going to be dependent on Records.Contact. What happens when the
company says Decca, but the contact is a person from A&R?

There are plenty of UI ways to help choose the contact, including linked
combobox/ listboxes; but I would think very hard about the design first.

Best wishes


Tim F