Order Form

[Guest]

Here is what i would like to do:

I have a purchase order form. On the main form there is a combo box to
choose a supplier. On the subform is the product name, price, quantity. The
product name is a combo box also. How can i make it so that when the
supplier is chosen on the main form, only the products available from that
supplier are displayed in the product name combo box on the sub form? Is
this possible?

Thanks in advance for the help!

 

[Brian Bastl]

Kat,

The rowsource for your products combo in the subform should include a Where
clause referencing the Supplier combo on the main form

The rowsource for your products combo should look something like the
following:
SELECT ProductID, ProductName
FROM tblProducts
WHERE (((tblProducts.SupplierID)=Forms!MyMainForm!SupplierCombo));

Then in the AfterUpdate event of the Supplier combo, you requery the
products combobox.

HTH,
Brian

 

[Guest]

Now you'll really think i'm an idiot, but can you tell me how to requery the
products combo box? when i try it i keep getting an error "there is no field
named productid in the current record". so, obviously i'm not referencing my
subform correctly. Help!

 

[Brian Bastl]

Kat,

To requery the combobox control in your subform, you'd use the following
syntax, changing the names within the brackets to your actual control names
on your form:

'main form
Private Sub YourSupplierCombo_AfterUpdate()

Me.[MySubformControl].Form![ProductCombo].Requery

End Sub

Note that the subform control (subform placeholder) is what needs to be
referenced, not the name of the actual form contained within the control.
These names may or may not be different.

HTH,
Brian