Microsoft KB Archive/112109

Knowledge Base

ACC: Cannot Use AutoNumber to Determine If Record Is New Record

Article ID: 112109

Article Last Modified on 1/18/2007

---

APPLIES TO

• Microsoft Access 2.0 Standard Edition
• Microsoft Access 95 Standard Edition
• Microsoft Access 97 Standard Edition

---

This article was previously published under Q112109

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

You cannot use the status of the AutoNumber field (or Counter field in version 2.0) to determine if the current record is the new record.

CAUSE

In Microsoft Access version 1.x, the Counter field was Null until the record was saved (that is, until you moved to the next record, or clicked Save Record on the File menu). Therefore, if you checked the Counter field (using the IsNull() function) and it was Null, then the record being edited was a new one. If not, then it was an existing record. The usual method was to put the following expression in the form's BeforeUpdate property:

IsNull([<counterfieldname>])

In Microsoft Access 2.0 and later, however, the AutoNumber (or Counter) field is updated as soon as you begin inserting a new record, which invalidates the method described above for Microsoft Access version 1.x.

RESOLUTION

In Microsoft Access 7.0 and 97

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

There is a property called NewRecord. You can check this property in the BeforeUpdate event of the form to determine if the record is a new one. For example:

1. Open the sample database Northwind.mdb.
2. Open the Categories form in Design view.

3. Add the following code to BeforeUpdate property of the form:

         Private Sub Form_BeforeUpdate(Cancel As Integer)
            Dim newmsg As String
            Dim newrec As Integer
            newrec = Me.NewRecord
            If newrec = True Then
                newmsg = "This is a new record"
                MsgBox newmsg
            End If
         End Sub
                           

4. Close the module.
5. Open the form in Form view and click the new record selector.
6. Type Bogus Category in the Category Name text box and click the new record selector again. Note that a message box stating "This is a new record" appears. Click OK and you will be on a new record.

In Microsoft Access 2.0

To determine if the current record is a new record using a Counter field in Microsoft Access 2.0, you can check the OldValue property. The OldValue property will return Null for a new record.

To do this, use the following expression in the form's BeforeUpdate property:

IsNull([<counterfieldname>].OldValue)

For another technique that does not require the use of a Counter field, please see the following article here in the Microsoft Knowledge Base:

112292 ACC: How to Determine if the Current Record Is the New Record

REFERENCES

For more information about the NewRecord property, search the Help Index for "NewRecord property."

For more information about the OldValue property, search the Help Index for "OldValue."

Additional query words: forms

Keywords: kbprb kbprogramming kbusage KB112109

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.