Opening form to correct record. Bookmark?

[stephenaa5]

I have a form [ScopeDTL], which sits on a query, the PK is ScopeID, which is a text field. This form will not allow the data to be edited. A button on said form opens a second form [ScopeDTLeditable], which as you can guess allows the data to be edited. This form is opened to the correct ScopeID. Once the edits are made, the form is closed. Since ScopeDTL was left open behind, the correct ScopeID is shown, however, with the old data. Pressing "refresh" shoots you back to the first ScopeID, which may be hundreds away.

I am looking to close ScopeDTLeditable, requery, and then have ScopeDTL display the ScopeID from which I left. I have gotten help on the following code, but it still doesn't work.

Any help would be appreciated.

Stephen.
------------code-------------

Private Sub Command156_Click()
Private Sub Form_Close()
Dim strScopeID As String
strScopeID = Nz(Me.ScopeID, "")
If strScopeID <> "" Then
Forms![ScopeDTL editable].Requery
Dim rst As Recordset
Set rst = Forms![ScopeDTL editable].RecordsetClone
rst.FindFirst "[ScopeID] = " & "'" & strScopeID & "'"
If Not rst.NoMatch Then Forms![ScopeDTL editable].Bookmark= rst.Bookmark


rst.Close
End If
End Sub
End Sub

[RuralGuy]

This link may help a bit.

[stephenaa5]

Thanks! I'll check it out.

[stephenaa5]

The problem I'm having is that my situation is a form over a query, not a form over a table. Any ideas?

[RuralGuy]

As I see it, your problem is returning to the same record after a requery which is the same whether using a query or a table.

[stephenaa5]

Actually, I was able to make his example work on the query too. But my new problem is that the Primary Key is not an autonumber. It's a string. When I try to manipulate his example to set as string, and then choose EmpFname, it fails.

Thoughts?

Thanks!

STephen.

[RuralGuy]

Strings require quotes:
rs.FindFirst "EmpID = '" & lngBookmark & "'"

[stephenaa5]

I'm sorely missing something simple...


Private Sub cmdRequery_Click()
Dim rs As Object
Dim lngBookmark As String

On Error GoTo Err_cmdRequery_Click
'set a variable to the current record
lngBookmark = Me.EmpFName
'requery the form
Me.Requery
'bring us back to the original record
Set rs = Me.RecordsetClone
rs.FindFirst "EmpID = '" & lngBookmark & "'"
Me.Bookmark = rs.Bookmark
Exit_cmdRequery_Click:
Set rs = Nothing
Exit Sub
Err_cmdRequery_Click:
MsgBox Err.Description
Resume Exit_cmdRequery_Click
End Sub

[stephenaa5]

Nevermind. I got stupid. Figured it out. Thanks!

[RuralGuy]

That's great! What was your final code? Do you want to follow the link in my sig and mark this thread as Solved?

[stephenaa5]

Here is what worked.
Thanks again.


Private Sub cmdRequery_Click()
Dim rs As Object
Dim lngBookmark As String

On Error GoTo Err_cmdRequery_Click
'set a variable to the current record
lngBookmark = Me.EmpFName
'requery the form
Me.Requery
'bring us back to the original record
Set rs = Me.RecordsetClone
rs.FindFirst "EmpFname = '" & lngBookmark & "'"
Me.Bookmark = rs.Bookmark
Exit_cmdRequery_Click:
Set rs = Nothing
Exit Sub
Err_cmdRequery_Click:
MsgBox Err.Description
Resume Exit_cmdRequery_Click
End Sub

[RuralGuy]

I made a couple of changes and used the code tags, the # tool.

Private Sub cmdRequery_Click()
   Dim rs As DAO.Recordset
   Dim strBookmark As String
   On Error GoTo Err_cmdRequery_Click
   'set a variable to the current record
   strBookmark = Me.EmpFName
   'requery the form
   Me.Requery
   'bring us back to the original record
   Set rs = Me.RecordsetClone
   rs.FindFirst "EmpFname = '" & strBookmark & "'"
   If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
   Else
      MsgBox "Could not locate [" & strBookmark & "]", vbOKOnly + vbCritical
   End If
   
Exit_cmdRequery_Click:
   Set rs = Nothing
   Exit Sub
   
Err_cmdRequery_Click:
   MsgBox Err.Description
   Resume Exit_cmdRequery_Click
   
End Sub