(Q) How do I tell when the user has moved to the blank record at the end of a table by
pressing the Next button on my form?
(A) In Acces 97/95, you can use the NewRecord property to detect
whether the current record is new or not. Directly from Access help, here's a
Sub NewRecordMark(frm As Form)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
MsgBox "You're in a new record." _
& "@Do you want to add new data?" _
& "@If not, move to an existing record."
Here's a function that you can call to tell you if you're on the new
record or not: (Read the comments by Allen Browne and Andy Baron.)
Function AtNewRecord(frm as
Dim varTemp as Variant
On Error Resume Next
varTemp = frm.Bookmark
AtNewRecord = (Err <> 0)
On Error Goto 0
This works because attempting to get the bookmark for
the new record triggers an error.
From Andy Baron:
Attempting to get the bookmark of a form as a way of determining if
you are on a new record is completely reliable, provided that this technique is only used
in the Current event of the form.
This code (AtNewRecord) is unreliable.
It doesn't work correctly under any version of Access. Access erroneously returns the
bookmark of the last accessed record while you are at a new record under some
circumstances. The code should work, and I consider it a serious bug in Access that it
To demonstrate the bug, take these steps:
1. Add a button to a form, with the code below in its Click event
2. Open the form.
3. Go to the last (or second last) record.
4. Go to a new record.
5. Type something into a text field, but do not save the new
6. Click the button.
You will see that the NewRecord property
reports correctly, but the Bookmark does exist.
The Bookmark is erroneous, and actually
matches the previously accessed record.
The moral of the story: never trust the
bookmark, unless you've tested NewRecord.
Of course, this problem is quite serious under Access 2 which did not have the
Private Sub cmdCheckIt_Click()
On Error GoTo Err_cmdCheckIt_Click
Dim sBM As String
Debug.Print "New Record is " & Me.NewRecord;
sBM = Me.Bookmark
Debug.Print " The Bookmark is " & Len(sBM) & " characters long."
Debug.Print " Error detected: " & Err.Number & " - " & Err.Description