Command Button to open form to specific record

[Jon M.]

Hi potential lifesavers, I have a form with a subform in it, frm1 and
subfrm1. frm1 displays a product, subfrm1 displays all customers who have
ordered that product in datasheet view. frm1 has a command button that runs a
macros to open a form called Customers. Customers displays all of the
customers info, address, etc. frm1 is linked to subfrm1 by productID and
subfrm1 is linked to Customers by customerID. My command button will only
open the form Customers, I would like it to open customers to the specific
record based on the record currently selected in the subform. Any
suggestions on this? I am sure there is a way to either change my my macro
to include a where condition or add code to affect it. I tried using this
code but got an error saying the field CustomerID couldn't be found:

DoCmd.OpenForm "Customers",,,"CustomerID = " & Me!CustomerID.Value

I assume this is because CustomerID is not a field on frm1, but I don't know
how to get it routed through the subform and you know what they say about
those who assume. Any help is greatly appreciated. Thanks

 

[Tal]

Hi Jon,

Have you thought about putting the command button on the subform?

Alternately, you need to reference the subform with the correct syntax.

DoCmd.OpenForm "Customers",,,"CustomerID" = " &
Me.Parent.subform1!CustomerID.Value
or
DoCmd.OpenForm "Customers",,,"CustomerID" = " &
Forms!frm1!subform1!CustomerID.Value

I haven't tried this in this context, but as long as the subform is open, it
should work.

Cheers,
Tal

 

[Jon M.]

I tried putting the command button on the subform, but because it's in
datasheet view I couldn't see it when I opened the form.