Go to next record in continuous subform skips to next record in parent form

Question

I have a subroutine to go to the next record on the click event of a command button that I have been successfully using to replace the built in navigation buttons for single-form-view subforms, but when I apply it to a continuous subform, the parent form changes to the next record. I want to prevent my users from filtering or searching, which is why I'm choosing to eliminate navigation buttons. How can I prevent the parent form from skipping to the next record when I have already set the focus to the subform?

Private Sub CmdNextRecord_Click()
On Error Resume Next
    Forms![frmParent]![fsubChild].SetFocus
    DoCmd.GoToRecord , , acNext
    Forms![frmParent].SetFocus
End Sub

Just like with the built-in navigation buttons: Place your own navigation buttons on the subform (in the footer). — Wolfgang Kais

Erik A Erik A · Accepted Answer · 2018-10-08T19:51:37

This line: Forms![frmParent]![fsubChild].SetFocus sets focus to the subform control on the main form, not the actual form inside the subform control.

To set focus to the first control within the subform:

Forms!frmParent!fsubChild.Form.Controls(0).SetFocus

I recommend you work with the forms recordset to move the record instead:

Dim rs As DAO.Recordset
With  Forms![frmParent]![fsubChild].Form
    Set rs = .RecordsetClone
    rs.Bookmark = .Bookmark
    rs.MoveNext
    If Not rs.EOF Then 'If not last record
        .Bookmark = rs.Bookmark
    End If
End With

Go to next record in continuous subform skips to next record in parent form

1 Answers