Filter combo box in subform based on field in main form

Hi,

I have a Form / Subform setup (Access 2007) where the main form displays
heading information for a purchase order. This PO heading information is
stored in a table named ‘PO_Master’. The subform shows lineitems for the
purchase order. These PO Lineitems are stored in a table called ‘PO_Detail’.
I have the subform linked to the main form by setting both the Link Master
and Child Fields equal to ‘PO_Num’, which is the field that both ‘PO_Master’
and ‘PO_Detail’ have in common.

The above setup is working fine, but I have a combo box in my subform that
allows the user to select a part from my parts file. Currently the ‘Row
Source’ for my combo box is as follows:

SELECT PartsTable.PartID, PartsTable.PartName FROM PartsTable;

This works, but it loads the combo box with *all* parts from the parts file.
I really would like it to only load the parts that correspond to the chosen
supplier for the currently selected PO (‘PO_Master’ has a ‘SupplierID’ field
that is also displayed on the main form).

Can anyone tell me if I can accomplish this by simply placing a WHERE clause
in the Row Source of the combo box? If this is possible, how should I
reference the SupplierID field in my WHERE clause?

…. WHERE PartsTable.SupplierID = PO_Master.SupplierID ????
or
…. WHERE PartsTable.SupplierID = Forms!MainForm!txtSupplierID ???? (where
txtSupplierID is a textbox on the main form that is linked to
PO_Master.SupplierID)

Or will both of these fail? If so, can anyone give me a hint or point me to
where I can find an explanation of how to do what I want?

Any help will be greatly appreciated,
Paul

--
Paul Kraemer

Why not try them before posting the question whether they will fail or not?

WHERE PartsTable.SupplierID = Forms!MainForm!txtSupplierID should work
but will need to be refreshed after record change of main form.
--
KARL DEWEY
Build a little - Test a little


"Paul Kraemer" wrote:

Hi,

I have a Form / Subform setup (Access 2007) where the main form displays
heading information for a purchase order. This PO heading information is
stored in a table named ‘PO_Master’. The subform shows lineitems for the
purchase order. These PO Lineitems are stored in a table called ‘PO_Detail’.
I have the subform linked to the main form by setting both the Link Master
and Child Fields equal to ‘PO_Num’, which is the field that both ‘PO_Master’
and ‘PO_Detail’ have in common.

The above setup is working fine, but I have a combo box in my subform that
allows the user to select a part from my parts file. Currently the ‘Row
Source’ for my combo box is as follows:

SELECT PartsTable.PartID, PartsTable.PartName FROM PartsTable;

This works, but it loads the combo box with *all* parts from the parts file.
I really would like it to only load the parts that correspond to the chosen
supplier for the currently selected PO (‘PO_Master’ has a ‘SupplierID’ field
that is also displayed on the main form).

Can anyone tell me if I can accomplish this by simply placing a WHERE clause
in the Row Source of the combo box? If this is possible, how should I
reference the SupplierID field in my WHERE clause?

…. WHERE PartsTable.SupplierID = PO_Master.SupplierID ????
or
…. WHERE PartsTable.SupplierID = Forms!MainForm!txtSupplierID ???? (where
txtSupplierID is a textbox on the main form that is linked to
PO_Master.SupplierID)

Or will both of these fail? If so, can anyone give me a hint or point me to
where I can find an explanation of how to do what I want?

Any help will be greatly appreciated,
Paul

--
Paul Kraemer