Limit values in subform

[Guest]

I have created a form for Purchase Orders, where the main form allows a
supplier to be entered, and the subform is where the detail lines of the
order are entered. The detail lines are based on a query from the products
table, which lists product codes, descriptions etc and the supplier of each
product. I would like to set it up so that if a supplier is selected in the
main form, the subfom only allows selection of products linked to that
supplier. Is this where the master/child relationship is based on two levels
of linking?

 

[Guest]

Basically you are asking for a cascading combo. There are plenty of threads
on this forum asking this question and it isn't difficult.

All you need in the subform is a combo box with a query as the record
source. The query has two columns. The first column is the list of products
and the second column is the supplier (which you make invisible). Put the
reference to your subform field in the criteria:

[forms]![main form name]![subform name].forms![field name]

and put the following code in the main form combo "after update" event:

[forms]![main form name]![subform name].forms![field name].requery

You will have to mess around with the reference to get it right but I think
that should be ok (but don't take my word for it)

 

[BruceM]

I will call the subform combo box from which you select the Product
cboProduct. The main form is frmPO; it is based on tblPO. SupplierID is
the primary key of tblPO (and the foreign key of the subform's Record Source
table). Substitute the names you are actually using.

You could make a row source query for cboProduct using the fields you have
described. In query design view, set the criteria for SupplierID to:
Forms!frmPO!SupplierID

In the subform's Current event:
Me.cboProduct.Requery