Filter a Subform based on Selection in Main form

[jenni]

Hello -
I have a purchase order form where the master form is
the "orders table" where you select the supplier.

The subform is the "Orders Details" table which allows
selection of the products.

On the subform, there is a dropdown to select the
products. How can I get only the products from the
selected supplier in the main form to show instead of all
of the products from all suppliers?

 

[JeffW]

Jenni,

assuming you have a foreign key to your Suppliers table in your Products
table, simply add the SupplierID from Products table to the rowsource of
your combo, and in the criteria line of your supplierID, add
Forms!NameOfParentForm!cboSupplier. You'll then use the afterUpdate event of
the cboSupplier to requery the cboProduct in your subform.

Since you didn't provide the names of your controls or forms, you'll have to
substitute the generic names with your names.

HTH,
Jeff

 

[jenni]

Jeff -
This worked, thank you. However, the "after update" only
re-queries after I leave the form and come back.

In the combox box on the subform, I set up the rowsource
just as you said with Forms!frm_orders!Supplier as the
criteria. Then, on the master form, I set the after
update property of the supplier combo box to
=[frm_Order_Details_ Subform].Form!SelectAProduct

Am I missing something? Is there a way to get this query
to run while I am still in the form?

 

[JeffW]

Hi Jenni,

Sorry, I should have been more specific. The requery needs to be done in the
AfterUpdate sub procedure of Supplier. To do this:

Open frm_Orders in design view
Right-click your Supplier combo to bring up Property sheet
Select "Events" tab
Click once on "After Update"
Click the ellipse (...) button to open the VB editor
Your finished code should look like the following...

Private Sub Supplier_AfterUpdate()
Me![frm_Order_Details_Subform].Form!SelectAProduct.Requery 'add this
line
End Sub

HTH,
Jeff