Return to row on tabular form

[Guest]

I have a tabular form (Continuous Forms) with a query as the Record Source.
The data in this form is not updatable because of the joins used in the
query. For each row in this form, frmUpdateESLOCS, there is a button which
takes the user to a 2nd form which allows him to make changes to the data for
that one row (using a RunSQL command).

When the user makes a change on the 2nd form and clicks the button to submit
the change, the 2nd form closes and the focus returns to row 1 of the 1st
form. This is not what I want. I want the user to return to the row he was
last on.

If the user does not make a change on the 2nd form and closes the form, the
focus returns to the row he was last on in the 1st form.

I need help figuring out how to return the user to the row he was on in the
first form.

Thank you,
Judy

 

[Guest]

If form 2 is not a modal form, it should be. That way you can put code in
the click event of the button that opens form 2 that will return you to the
record you were on.
You need to have a unique field or fields in form 1's recordset that you can
save before you open the form, then when form 2 closes, the code will resume
and you can use the findfirst method to move back to the row. Here is an
example, you will have to modify the names for your situation:

Private Sub cmdOpenForm2_Click()
Dim lngNdx as Long

lngNdx = Me.txtKeyField
Docmd.OpenForm "Form2", acNormal, , , , acDialog
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngNdx
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

 

[Guest]

Thank you very much for responding. I used your example and it does exactly
what I want it to.

Thanks again!
Judy

If form 2 is not a modal form, it should be. That way you can put code in
the click event of the button that opens form 2 that will return you to the
record you were on.
You need to have a unique field or fields in form 1's recordset that you can
save before you open the form, then when form 2 closes, the code will resume
and you can use the findfirst method to move back to the row. Here is an
example, you will have to modify the names for your situation:

Private Sub cmdOpenForm2_Click()
Dim lngNdx as Long

lngNdx = Me.txtKeyField
Docmd.OpenForm "Form2", acNormal, , , , acDialog
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngNdx
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
--
Dave Hargis, Microsoft Access MVP

I have a tabular form (Continuous Forms) with a query as the Record Source.
The data in this form is not updatable because of the joins used in the
query. For each row in this form, frmUpdateESLOCS, there is a button which
takes the user to a 2nd form which allows him to make changes to the data for
that one row (using a RunSQL command).

When the user makes a change on the 2nd form and clicks the button to submit
the change, the 2nd form closes and the focus returns to row 1 of the 1st
form. This is not what I want. I want the user to return to the row he was
last on.

If the user does not make a change on the 2nd form and closes the form, the
focus returns to the row he was last on in the 1st form.

I need help figuring out how to return the user to the row he was on in the
first form.

Thank you,
Judy

 

[Guest]

Glad I could help, Judy
--
Dave Hargis, Microsoft Access MVP

Thank you very much for responding. I used your example and it does exactly
what I want it to.

Thanks again!
Judy

If form 2 is not a modal form, it should be. That way you can put code in
the click event of the button that opens form 2 that will return you to the
record you were on.
You need to have a unique field or fields in form 1's recordset that you can
save before you open the form, then when form 2 closes, the code will resume
and you can use the findfirst method to move back to the row. Here is an
example, you will have to modify the names for your situation:

Private Sub cmdOpenForm2_Click()
Dim lngNdx as Long

lngNdx = Me.txtKeyField
Docmd.OpenForm "Form2", acNormal, , , , acDialog
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngNdx
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
--
Dave Hargis, Microsoft Access MVP

I have a tabular form (Continuous Forms) with a query as the Record Source.
The data in this form is not updatable because of the joins used in the
query. For each row in this form, frmUpdateESLOCS, there is a button which
takes the user to a 2nd form which allows him to make changes to the data for
that one row (using a RunSQL command).

When the user makes a change on the 2nd form and clicks the button to submit
the change, the 2nd form closes and the focus returns to row 1 of the 1st
form. This is not what I want. I want the user to return to the row he was
last on.

If the user does not make a change on the 2nd form and closes the form, the
focus returns to the row he was last on in the 1st form.

I need help figuring out how to return the user to the row he was on in the
first form.

Thank you,
Judy