Using lookup to limit another lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Desiree Keener - 15 Dec 2003 21:04 GMT

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.

Reply to this Message

Nikos Yannacopoulos - 16 Dec 2003 12:56 GMT

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

>-----Original Message-----
>I am trying to use the value from one lookup field to
[quoted text clipped - 13 lines]
>Thanks for any help with this.
>.

Reply to this Message

Tim Ferguson - 16 Dec 2003 17:16 GMT

> 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

Reply to this Message