Problem opening form to a specific record from subform

[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=" & myOrder, 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.

[RuralGuy]

If OrderID is a text value then you will need your WhereCondition argument to be: "OrderID='" & myOrder & "'"

[bpepper82]

If OrderID is a text value then you will need your WhereCondition argument to be: "OrderID='" & myOrder & "'"

OrderID is an integer. I as habit label all my text boxes starting with txt.

[RuralGuy]

What you described in the first post is a WhereCondition that has no valid match. It is as if it is an invalid WhereCondition.

[bpepper82]

What you described in the first post is a WhereCondition that has no valid match. It is as if it is an invalid WhereCondition.

It is picking up the value from txtOrder, but rather than picking up the value for the line in the subform that I click on, it is picking up the value for the first line displayed in the subform. If I open the subform on its own, then it works correctly. It's only not working when opened as a subform.

[RuralGuy]

Assuming your SubForm is in Continuous View mode, The current record changes when you move the focus on the SubForm by simply selecting another record. The Me.txtOrder "should" use the value of that control on the current record. The current record *will* change before the Click event code you posted unless you are doing something else. Add some msgboxes to the events so you can see what is really happening. Either that or single step the code.

[bpepper82]

Assuming your SubForm is in Continuous View mode, The current record changes when you move the focus on the SubForm by simply selecting another record. The Me.txtOrder "should" use the value of that control on the current record. The current record *will* change before the Click event code you posted unless you are doing something else. Add some msgboxes to the events so you can see what is really happening. Either that or single step the code.

Thanks for answering so quickly. I do have the SubForm in continuous view. I've done the message box thing and single stepped the code, and both tell me that it is picking up the first record regardless of which one I click on. Those lines execute and then the form opens, nothing else is happening in between.

[RuralGuy]

Comment out the openform code and just use a MsgBox to display the Me.OrderID value. Does it change each time you select a different record in the SubForm?

[bpepper82]

Comment out the openform code and just use a MsgBox to display the Me.OrderID value. Does it change each time you select a different record in the SubForm?

Yes, every one I click on gives the value for the first record.

[RuralGuy]

Is your code in the SubForm? How about is you select a different control in the SubForm 1st and then the one with the code?

[bpepper82]

Is your code in the SubForm? How about is you select a different control in the SubForm 1st and then the one with the code?

My code is in the subform. I just tried selecting another control first and got the same result.

[RuralGuy]

Try importing your db into a fresh new db and see if the problem follows. It only takes a minute: http://www.btabdevelopment.com/ts/impnew

[bpepper82]

Try importing your db into a fresh new db and see if the problem follows. It only takes a minute: http://www.btabdevelopment.com/ts/impnew

Unfortunately the problem does persist

[RuralGuy]

Do you have the SubForm locked or something? It is not changing the Current Record and that is not right. Can you post your db maybe? Remove any sensitive data of course.

I did notice you were passing the word "Edit" to the next form in the OpenArgs argument. Could it be something you are doing in the OnLoad or Open event of the next form?

[bpepper82]

Do you have the SubForm locked or something? It is not changing the Current Record and that is not right. Can you post your db maybe? Remove any sensitive data of course.

I did notice you were passing the word "Edit" to the next form in the OpenArgs argument. Could it be something you are doing in the OnLoad or Open event of the next form?

I cannot upload the db unfortunately. One of those things working for a bank, they are very strict on security. I don't think it has anything to do with the OnLoad event on the form that opens, because I can isolate the problem to the OnClick event in the subform. I did just confirm that the subform is not locked.