How do I go to my last entered record in a large access form?

Question

I have a large Access database of items (70,000+) with a one-to-many relationship. Each record has a set of analytical results e.g. record 1 has sample IDs sample1: chlorine=1mg/l, sample 2: chlorine=2.3mg/L; record 2 has sample IDs sample1: chlorine=3.8mg/L.

I have created a form to begin entering data, but I would like to create a macro button that will take me to the last record entered. Once I start getting to record #100+, I do not want to have to remember the number of the last record I was on.

To complicate this issue, some records do not have analytical data and will be null.
I was thinking of a macro that will search for null records. Once it finds a null record, it will double check that it isn't a previously entered record that happened to be null by checking the next 50 or so records and make sure each 50 records ahead of it are also null. Could someone give me some guidance on how to do this with a macro? Once I have a working macro, I know how to apply it to a button on my form.

Thank you.

Answer 1

You could create a table to store a bookmarked record number from your form:

Then set your form up so you have a command button to bookmark your place:

Then on that button's click event you could take the record number of the current record and put it in your bookmark table for later:

Private Sub cmdSavePlace_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String

    Set db = CurrentDb

    sql = "DELETE * FROM tblLastAccessedRecord"
    db.Execute sql

    Set rs = db.OpenRecordset("tblLastAccessedRecord")

    With rs

        rs.AddNew
        rs!RecordNumber = Me.CurrentRecord
        rs.Update

    End With

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

You can then add a command button to your form to move you to the next record after the saved bookmark using the record number that's currently stored in the table:

The following VBA should jump you to the record after (+1) the one that's been stored in your bookmark table:

Private Sub cmdGoToNext_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblLastAccessedRecord")

    DoCmd.GoToRecord acDataForm, "frmDataEntry", acGoTo, rs!RecordNumber + 1

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

So, for example, if I bookmark record 11...

...this is now stored in our table:

So if we close the form for the day, and come back to form tomorrow we might be back at record 1...

...but if we click our go to bookmarked record command button we'll be taken to record 11+1, i.e. record 12: