Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
10 Commandments 

In Memoriam

Terms of Use

VB Petition

Forms: Detect new record in a form

Dev Ashish &
Allen Browne &
Andy Baron

(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 generalized function.

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."
    End If
End Sub

   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 Form)
  Dim varTemp as Variant
  On Error Resume Next
  varTemp = frm.Bookmark
  AtNewRecord = (Err <> 0)
  On Error Goto 0
End Function

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.

From Allen Browne:

        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 does not.

    To demonstrate the bug, take these steps:
    1. Add a button to a form, with the code below in its Click event proc.
    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 entry.
    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 NewRecord property.

'********* Code Start **********
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."
    Exit Sub
    Debug.Print " Error detected: " & Err.Number & " - " & Err.Description
    Resume Exit_cmdCheckIt_Click
End Sub
'********* Code End **********

1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer