Filter subform combobox from mainform combobox

[WoodyAccess]

I have a main form (OrdersForm) which contains Order No, Supplier Name (combo
box) and Order Date.

I have a subform (SupplyOrderDetails) which contains Order No, Product
(combo box), Unit measurement, and Unit Price, and Quantity.

When I select the SupplierName on the main form I want the Product combo box
on the subform to only display products from that supplier.

Also, when the product combobox product is selected I want all the other
information on the subform to be automatically filled out from the
information I have on my Products form

Can this be done? If so how? (I am slowly learning Access so go easy on the
jargon, thanks)

 

[BruceM]

What I did in a similar situation was to set the combo box row source in the
main form's Current event:

strRowSource = "SELECT ProdDescr, ProductID, ProdCode, ProdUnit,
UnitPrice, SupplierID " & _
"FROM tblProduct " & _
"WHERE SupplierID = Forms!frmPO!SupplierID " & _
"ORDER BY ProdDescr, ProdUnit"

Forms!frmPO!fsubPO_Items.Form!cboDescription.RowSource = strRowSource

I trust the field names are self-explanatory. My main form is named frmPO,
and my subform is named fsubPO_Items.

On the subform the combo box bound column is 2 (the primary key from the
Products table); it is bound to ProductID in tblPO_Items (the subform's
record source). UnitPrice is bound to UnitPrice in tblPO_Items because I
decided the best way to keep track of the price at the time of purchase is
to store that value rather than create a new record in tblProducts each time
the price changes. The other fields are displayed in unbound combo boxes on
the subform. For instance, the ProdDescr text box has as its Control
Source:
=Me.cboDescription.Column(0)
The Column property starts counting at 0, so it is the first column in the
combo box row source as defined above.
The subform's Current event requeries the combo box:
Me.cboDescription.Requery
In my case I set the column count in the cboDescription properties to 6, the
bound column to 2 (on the property sheet the column count starts at 1 rather
than 0, so it really is the second column), and the column widths to
something like 1";0";.5";.75";.5";0". This lets the user see whether the
item is, say, as 1-gallon bucket or a 5-gallon bucket (ProdUnit), etc.
This seems to work, but I have not tested it extensively. I don't know for
sure that this is the best way, and would be interested in hearing from
others who are more experienced than I, but since I recently addressed a
similar situation I am passing along what I learned.