Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Forms: Detect new record in a form

Author(s)
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_cmdCheckIt_Click:
    Exit Sub
Err_cmdCheckIt_Click:
    Debug.Print " Error detected: " & Err.Number & " - " & Err.Description
    Resume Exit_cmdCheckIt_Click
End Sub
'********* Code End **********

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