Microsoft KB Archive/112109

= 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([ ])

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.



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.</li></ol>

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([ ].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

<div class="references_section">