How to navigate to previous records depending on textbox-CodePudding

I have a button on my form whenever I click on it, it creates several records including a column named: [Aantal], if aantal says 5, it will create 5 records. This column is also as a textbox named [Aantal] on the form.

After the records are created it goes to the last record as shown here:

    DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acLast

Is it possible to make it navigate to the first record of those 5 created?

So not the very first record in the table, but the first of those just created. For example if [Aantal] is 5, make it go back 4 records, that way it will be on the first of the 5 records it just created.

Edit:

I have something like this that goes to the latest record, now I want it to go to the first record instead:

pgn = Me.CurrentRecord
t1 = Nz(Me.Aantal, 1)
If t1 > 1 Then
t2 = pgn   t1 - 1

CodePudding user response：

You can also use acPrevious and acNext instead of acLast. See: AcRecord enumeration (Access). But you must make the calculation of how many times you must move to get to the desired record yourself. Access does not know which records you've just created.

Dim t1 As Long, i As Long

t1 = Clng(Nz(Me.Aantal, 1))

DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acLast
For i = 1 To t1 - 1
    DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acPrevious
Next i

Or you can directly jump to it with

Dim rs As DAO.Recordset
Dim t1 As Long, numRecords As Long

t1 = Clng(Nz(Me.Aantal, 1))
Set rs = Me.RecordsetClone
rs.MoveLast
numRecords = rs.RecordCount
rs.Close

DoCmd.GoToRecord acDataForm, "GeleidelijstForm", acGoTo, numRecords - t1   1

You can also jump to a specific record like this in case you are able to identify the record by some value

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MyID] = " & theId
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close

CodePudding user response：

You can use the RecordsetClone and loop backwards a number of times. Something like:

Private Sub cmdPos_Click()
    On Error GoTo E_Handle
    Dim rs As DAO.Recordset
    Dim lngLoop1 As Long
    Dim lngCount As Long
    lngCount = Me!txtPos - 1
    Set rs = Me.RecordsetClone
    rs.MoveLast
    If lngCount > 0 Then    '   if only one record added then no need to loop
        For lngLoop1 = 1 To lngCount
            rs.MovePrevious
        Next lngLoop1
    End If
    Me.Bookmark = rs.Bookmark
sExit:
    On Error Resume Next
    Set rs = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "cmdPos_Click", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub