P
Paul Kraemer
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
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