Problem opening form to a specific record from subform (1 Viewer)

[bpepper82]

I have a form that displays details for a specific asset and a continuous subform that lists all the purchases for that asset. I have the following code in the On Click event for one of the fields in the subform:

Dim myOrder As Integer
myOrder = Me.txtOrder
DoCmd.OpenForm "frmOrders", acNormal, , "OrderID=" & Order, acFormEdit, , "Edit"

The problem is, when I click on any item in the subform, the code returns the value of the first item in the form rather than the one clicked on. If I open the subform on its own (outside the main form) then the code works fine. I can't figure out why it won't work in a subform.

 

[James Dudden]

Try this:

DoCmd.OpenForm "frmOrders", acNormal, , "OrderID=" & Me!Order, acFormEdit, , "Edit"

I assume 'Order' is the control name with the value you want to filter to.

 

[sensetech]

... or should your code be

DoCmd.OpenForm "frmOrders", acNormal, , "OrderID=" & myOrder, acFormEdit, , "Edit"

... so that it uses the integer variable you populated from Me.txtOrder?



By the way, do you have Option Explicit set in your code module?

 

[bpepper82]

Thanks guys. I've tried both ways with the same result. Also, yes I do have Option Explicit set in that module. There was a typo in the code I posted (should have been "OrderID =" & myOrder)

 

[sensetech]

Add a MsgBox to the OnClick code to display the value of Me.txtOrder. At least that should tell you whether you're even trying to pass the required value to frmOrders

 

[bpepper82]

Add a MsgBox to the OnClick code to display the value of Me.txtOrder. At least that should tell you whether you're even trying to pass the required value to frmOrders

I've tried that. No matter what line I click on, it always returns the value from Me.txtOrder from the first record listed. It only does this in the subform. If I open the subform in form view, it returns the value of me.txtOrder for whatever record I select.

 

[James Dudden]

You say that the code is on the OnClick event and then you click a field in the subform. Can you confirm that the OnCLick event is on the control and not the form. Also that the control you are clicking is within the Detail section of the form and the control me.txtOrder is also within the Detail section?

I do this all the time without a problem so there's got to be something simple we're missing. For what it's worth I would normally do it either on the Dbl-click of the control (within the detail) or on a button (again within the detail).

The final thing to check is that you don't have a name conflict going on either with a public variable or other control with the same name so I would double-check those things.