Combo boxes and subforms

[Richio]

I have set up a form with two combo boxes -
the first is a "category" drop down
the second combo box is on a filtered query which only selects the criteria of a particular category.

Ie first combo select "fruit" as the category, second then only lists apples, pears etc (using [Forms]![Order Details]![Category] in the criteria)

After alot of trial and error it now works fine.....on this form....but

If I drop the form as a subform into another form it will not work - when the category is selected in the first combo an error message "Run time error 2488 - You can't use the Apply Filter action on this window" and debugs to VB on the after update on the first combo where I have DoCmd.Requery "secondcombo"entered.

By removing this it does not work as it only picks up the category first entered on the first entry (hence having to requery) but does not come up with an error- it is in datasheet view so many lines are entered in the subform.

I have seen various other posts on this (filtered combos), but I just seem to be so close this way its driving me mad

Many thanks

 

[Elana]

Be sure you are referring to the subform correctly - check out this MS Knowledge Base article

http://support.microsoft.com/default.aspx?scid=kb;en-us;209099

HTH
Elana

 

[Richio]

I have checked out the code from the ms knowledgebase and needed to amend as code in subforms is different to that in a form, but it is still coming up with the same error unfortunately.

Does a filtered query on a combo box work in a subform?

Thanks

R

 

[Elana]

I don't think I can help you unless I see what you're talking about. Can you post your db?

 

[Richio]

Elana

Attached is db - not fully functional but should get the idea -

If the Order details SubForm is opened only, by selecting the category in the first combo only the products in that category are shown in the next combo

However in the Orders form (containing the Order Details subForm) when performing the same, the error occurs.

I can't help but think at the moment that I am trying to go about this the wrong way as there are other posts which attempt to acheive the same but using a different technique!

Thanks

Richio

 

[Richio]

Ooops

As I copied the tables / forms to a new db I forgot to include the relationships on the tables on the attached - does not seem to interfere with my problem however

Sorry

 

[Elana]

Instead of

docmd.requery "product id"

put this:

me.productid.requery

Works fine then

E

 

[WayneRyan]

Rich,

In your event for the Category Combo it should be:
Me.ProductID.Requery

And the source for the Product combo contains:
[Forms]![Order Details SubForm]![Category]
which is not available (form not open)

Wayne

 

[Richio]

Many thanks for the help......still a few problems when entering more than one product but I have managed to work out a sloution to that.

Now I can move on to the next stage. Thanks again