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

ID: Q112109

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

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:

   ARTICLE-ID: Q112292
   TITLE:      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          : kbusage FmsHowto 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb

Last Reviewed: November 21, 1998