ACC: Cannot Use AutoNumber to Determine If Record Is New Record (112109)

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:

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

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

Close the module.
Open the form in Form view and click the new record selector.
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