Nested table field lookup question

[Peter Boulton]

Hi,

I'm a bit of a newbie on Access, so please excuse me if this is a basic
question!

I have 2 joined tables:

Licensors Control
--------- -------
Licensor -- 1 ------- 00 -- Licensor
Product

Licensors has a one to many relation with Control, joined on Licensor.

If I view the Licensor table in Access (2003) I get a nested view into
Control - i.e. I click the little '+' on a Licensor and the matching
records from 'Control' appear in a nested table.

I am using this option to allow users to update Control. I want
'Product' to be a combo-box lookup of past values of 'Product', but only
the Products taken by the current value of Licensor in the parent table,
Licensors.

How do I constrain the pop-up to just the Products for the Licensor
who's '+' on Licensors I just clicked on?

Obviously I know about query criteria (I'm not a total newbie!), but in
this case, am confused about how I could refer to the 'parent' value of
Licensor within the critera for the lookup query. How can I make the
query 'know' the parent Licensor?

I hope I have explained this clearly! Tips / advice gratefully received!

Thanks.

Pete

 

[John Vinson]

How do I constrain the pop-up to just the Products for the Licensor
who's '+' on Licensors I just clicked on?

You cannot, in table datasheet view.

But then users should never see Tables at all. Instead, create a Form
with a Subform. You can create a combo box on the subform based on a
Query selecting only the licensor's products; use a criterion of

=[Forms]![yourform]![controlname]

to select the appropriate values of the licensor ID.

John W. Vinson[MVP]