Show certain records only on subform

[JayGee1969]

No, I haven't solved it yet, just not had chance to look at it since my last post on Friday. Please bear with me as I am juggling different tasks, of which this is only one. I do appreciate your continued help, thank you for your patience.

[John_G]

Good morning -

You were a lot closer to getting it to work than you thought.

In your Customer Order form, In the After Update (I moved it from Before Update, but that's not critical) event of CustomerID, fix the code to this:

Me.[Orders SubForm].Form!ProductCode.RowSource = "Select ProductID from products where CustomerID = " & Me!CustomerID
Me.[Orders SubForm].Form!ProductCode.Requery

Add that was needed was to change the Recordsource to RowSource, and to add the square braces around [Orders Subform] (required whenever there is a space in an object name).

Add the same two lines to the On Open event of the Customer Order form, so that the product list is correct when the form is first opened.

Add the same code in the same places in the Customer Order by order ID form.

I strongly suggest you do not allow the CustomerID to be changed in the Customer Order by order ID form, otherwise you could find yourself in a real mess, because the data in the OrderDetails table would be wrong.

There are other things that need fixing (e.g. Customer Orders subform1 has an error) , but the above should get you moving again.

Cheers!

John

[JayGee1969]

Thanks John - tried that but got a Microsoft Visual Basic Run-time error '2465': Microsoft Access can't find the field '|1' referred to in your expression.

As I have renamed my forms now to frmCustOrd1 and frmCustOrd1Sub do I take it I don't need the square brackets any more?

[Rainlover]

There is no harm in using the Square Brackets but you must use them when you have spaces in the Names. You should not be using spaces anyway.

[John_G]

Hi -

If you have renamed the subforms, and used those names in the code I used, that is the error. In a line like this:

Me.[Orders SubForm].Form!ProductCode.RowSource = "Select ProductID from products where CustomerID = " & Me!CustomerID ,

[Orders Subform] is not the name of the subform - it is the name of the control on the main form that contains the subform. When you rename the subform, you need to change the Source Object property of that control to the new name of the subform. But as long as you don't change change the name of the control itself, the code can be left as it was.

I am assuming that the product list combo box on the subform is still called ProductCode.

John

[JayGee1969]

John

Thank you so much - I could kiss you xxx. That has solved the problem and I really appreciate your patience with me and getting me sorted.

Many, many, many thanks, and thanks also to Rainlover for trying to put me on the right path.

[John_G]

Thank you for the kind words - much appreciated. We're glad we are able to help.

All the best as you continue with Access.

John (and others)