Filter subform combobox by Form combobox

[K@foss-dental]

I have a main form (frmPurchaseOrders) which allows a user to select a
supplier using a combobox. I want a combobox in the subform
(frmPurchOrdSfrm2) to display the list of products unique to the supplier
selected, which the user can then choose from. The main form is based
directly on a table, but the subform is based on a query
(qryPODetailsSorted). I have the structure now, but the subform drop-down
display all products from all suppliers. Is there any way to accomplish what
I want without elaborate (VBA) code?

 

[Ron2006]

You want to put a criteria in the query to point to the combo box AND
in the afterupdate of the combobox have it requery the subform combo
box andor the subform itself

 

[K@foss-dental]

Can you give me more specifics? Which query field do I attach the criteria
to? (I have tried it several ways.) I have used
"[Forms]![frmPurchaseOrders]![SupplierName]" to reference the form combo box
and I get all or nothing results depending on where I place the reference;
never a filtered result. I also have a requery macro in the AfterUpdate
field. It seems to have no effect, I'm sure due to not having the filter in
the right place. I have left the requery detail section of the macro blank.
What syntax should I use to reference the subform &/or its control? My
RowSource for the subform combobox references the linked tables tblProducts
and tblSuppliers.

 

[Ron2006]

in the design view for the query "qryPODetailsSorted" on the criteria
line for the supplier name you should place
"[Forms]![frmPurchaseOrders]![SupplierName]"

on the form frmPurchaseOrders in the afterupdate event of the combo box
that you use to select the supplier you should put
me![frmPurchOrdSfrm2].form.[productscomboname].requery.

(The extra "form" may or may not be necessary, but I think it is. the
code assister should popup what you can get to so experiment until you
can get requery to show up as an option.)

 

[K@foss-dental]

Ron,
Thank you for your help so far; I am half-way there. I have managed to
limit the products to the selected supplier using the criteria you suggested
in the qryPODetailsSorted query. However, the requery function does not
work. When I move to the next record, I get a blank field in the products
combo and the drop down list yields only the list of products from the
supplier on the first page (record.) I have tried a requery function in the
AfterUpdate, Got Focus, LostFocus of the form, subform, field, etc., to no
avail.
Any idea what I am doing wrong?

 

[Ron2006]

try the oncurrent event on the same form that you have the other
requeries. that should make it so the query supplies nothing until the
user selects a company.

 

[K@foss-dental]

I tried that. It throws the program into an endless loop of requeries and I
have to shut the program down. I want the requery to work when changing from
record to record, not just when a user selects a supplier on a new record.
Otherwise, when the record changes and a different supplier is in the main
form combo box, I get a blank combo box on the subform, and as I said, the
drop down list remains anchored to the first record.
Is there something akin to an OnNextRecord command that can be used to key
the requery?

 

[K@foss-dental]

Please note that I have read hosts of posts about this problem. Many people
seem to have a similar problem (requery), but none of the solutions I have
read applies or works in my case (elaborate VBA answers not withstanding...)

 

[Ron2006]

Just so I better understand, the combobox in frmPurchaseOrders is a
bound combobox? This form is datasheet, single, or continuous?


And is frmPurchOrdSfrm2 a child of the PurchaseOrders form or is it
simply a subform with a query pointing to the Purchase orders form? is
this form a datasheet form or single form or continuous form?

 

[K@foss-dental]

Ron,
Lets's see if I can explain this properly. The form frmPurchaseOrders is a
single form based directly on the PurchaseOrders table. It contains 2
subforms: the first subform, a single form, is based on the Suppliers table
and linked (master-child) via the SupplierID field. It merely supplies
details about the selected supplier.
The second subform is a continuous form and is NOT a subform of the 1st
subform. It is based on a query of the Products and PODetails tables joined
via the ProductID fields (qryPODetailsSorted) and linked (m/c) to the main
form via the POID field.
The Control Source for the combo box on the main form is the SupplierID
field and the RowSource is qrySuppliersSorted, a single table query.
The Control Source for the 2nd subform combo box is the ProductID field and
the Row Source is qryProductsForCombo, a query of the Products and Suppliers
tables joined via the SupplierID fields. (I try to name my tables, queries,
forms and fields something that fairly describes their function.)
Hope that all means something to you. It sounds confusing to me and it's my
database!
~K

 

[Ron2006]

I believe we still need to do a requery, but I think the requery should
be on the oncurrent event for the 2nd subform which is NOT where I told
you to put it before. I have also seen some do the requery of the
combobox source in the gotfocus event of the combo box itself.

But try the oncurrent event first.

 

[K@foss-dental]

Nope. In the OnCurrent it goes into a loop (I believe because it perpetually
requeries) and aborts the program. In the Got Focus I now get an error
message saying that I cannot apply a filter to the selected object because no
object has been selected. When I use a SelectObject (frmPurchOrdSfrm2)
command in the macro, it says the object is closed. If I use the OpenForm
command, as expected, it opens the subform in a new window.
Arrrrgggghhhhh!!!!!!