MS Access DoCmd.GoToRecord doesn't work after Form Requery-CodePudding

I have a main form and a sub form (overview_Form).

On the main form I have some text boxes and two buttons to add records to a table details.

The sub form is a continuous from that shows the content of table1, onl oad the sub form show the last 10 records, and the rest of the records can be seen by scrolling up.

What I am trying to do is whenever a new record is added the sub form must be updated and after that show the last 10 records again.

Here how I am doing this but does not work

Public Sub Update_From()
    Me.Form.Requery
End Sub

Public Sub Update_Goto()
    Update_From
    Dim ID As Long
    ID = (DMax("ID", "details"))
    If ID < 10 Then
        ID = 1
    Else
        ID = ID - 10
    End If
    DoCmd.GoToRecord , , acGoTo, ID
End Sub

Both functions work individually, but when the Update_form is called and then the DoCmd.GoToRecord is called, the DoCmd.GoToRecord part does not work, i.e. the form shows the first record instead of the specified ID.

The procedures are triggered by a button on the main form as follow: When the button is clicked a procedure that sits behind the main form called Goto_Record is called. This procedure then calls another procedure that sits behind the subform, which is called Update_Goto, the one shown above, is called.

In main form:

Private Sub Goto_Record()
    Form_overview.Goto_Record
End Sub

I appreciate any help.

CodePudding user response：

If I understand this arrangement, main form has button that calls procedures sitting behind subform. In order to execute GoToRecord method against subform by calling procedure from main form, have to modify code. Add a line that sets focus to the subform.

Me.Parent("yourSubformContainerName").SetFocus
DoCmd.GoToRecord , , acGoTo, ID

More info and where I found the answer Move to next record in a MSAccess ADO Subform using VBA

Assume you understand this method of subtracting from max ID for determining offset works as long as subform records are not filtered nor sorted other than ID ascending and there are no gaps in ID sequence of last 10 records.

Another approach that doesn't have those drawbacks.

    Update_From
    With Me.Recordset
        If .RecordCount > 10 Then
            .MoveLast
            .Move -9
        End If
    End With