Combo Boxes and Subforms

[acass70391]

I have an mdb that is used for Purchase Orders. The User enters the Vendor in a combo box in the PO Header form. In the subform, the user enters the Part Number (also a combo box), Qty, and Price.

I have several vendors and they sell several different (and several of the same) products, all at varying prices.

In the subform's Part Number Combo Box, I'd like the drop list to show only the Part Numbers (and prices) that are sold by the Vendor chosen in the Header. By limiting the list, I only see what that specific Vendor sells.

Thanks in advance for your help! If you'd like a copy of my database (zips up to 30kb), e-mail me at ftnvlygirl@hotmail.com.

 

[kerplunkitt]

Sounds like you need to create a query and then base your combo box on the query.
In the query itself you need to specify in the criteria, the form name and the header field, so when you get to the vendor name and then click on the combo box, the underlying query looks at the vendor name and then shows the items that the vendor has available.
The criteria should be something like:
Forms!Formname!vendorfieldname
If there are spaces then enclose them in these things[] like this [form name]![vendor field name].
This is what i would try - Hope this helps.

 

[hanosm]

Try creating a new query based on your tables that shows, for each vendor, the vendors products.

For the following I am assuming that you in your vendor table you have VendorID and that this is what the combo box in PO header form returns. Amend this where necessary.

In the query you have created, in the criteria section under the VendorID column, build a statement that references the criteria to the combo box on the PO header form.

Once this is done, create another form with a combo box on it. The record source for this combo box should be the query you have just created. Then place this form as a subform on your main form.

The final thing to do, is to enter "me.refresh" in the after update property of the vendor combo box on the main PO header form.

Try this and please let me know if it works!!

I'm not sure that this is the RIGHT way to do this, but this is the way I do it and it works fine!!!

Hanif