Solved Switch between form and datasheet view and stay on current record (1 Viewer)

[frankt68]

Hi!

I have a form, that opens in Add New Record mode, using this code

Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, Me.name, acNewRec
End Sub

For switching between the form and the datasheet view, I use this code:

Private Sub Form_DblClick(Cancel As Integer)
'The CurrentView property uses the following settings.
'Setting     Form displayed in:
'0   Design View
'1   Form View
'2   Datasheet View
'7   Layout View

If Me.CurrentView <> 2 Then
   RunCommand acCmdDatasheetView
   DoCmd.ShowAllRecords
Else
   RunCommand acCmdFormView
End If

This works fine, except it always goes to the first record when I switch from the form view to the datasheet view, but I'd like to stay on the current record.

How can I achieve this?

When I switch from the datasheet view to the form view, it stays on the current (selected) record.

 

[ebs17]

stay on the current record

This will not succeed with actions that cause the data source to be retrieved again. But you can remember the ID of the record and then set this record to active again.

Dim lngID As Long
lngID = Me.txtID
' action with requery
Select Case Me.CurrentView
   Case 1
       Me.CurrentView = 2
   Case 2
       Me.CurrentView = 1
End Select
Me.Recordset.FindFirst "ID =" & lngID

 

[frankt68]

This will not succeed with actions that cause the data source to be retrieved again. But you can remember the ID of the record and then set this record to active again.

Dim lngID As Long
lngID = Me.txtID
' action with requery
Select Case Me.CurrentView
   Case 1
       Me.CurrentView = 2
   Case 2
       Me.CurrentView = 1
End Select
Me.Recordset.FindFirst "ID =" & lngID

Thanks, that was helpful.