Microsoft KB Archive/210411

= ACC2000: Cannot Use AutoNumber to Determine If Record Is New Record =

Article ID: 210411

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210411





SYMPTOMS
IsNull([AutoNumber Fieldname]) no longer returns True while entering a new record.



CAUSE
In Access, the AutoNumber field is updated as soon as you begin inserting a new record.



RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Access includes a property called NewRecord. You can check this property in the BeforeUpdate event of a form to determine if the current 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 the 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 box and click the new record selector again. Note that a message stating, "This is a new record" appears. Click OK and you will be on a new record.</li></ol>

<div class="moreinformation_section">

MORE INFORMATION
In 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). If you checked the Counter field (using the IsNull function) and it was Null, then the record being edited was a new record. If not, then it was an existing record. The usual method was to put the following expression in the form's BeforeUpdate property:

If IsNull([<CounterFieldName>]) Then ... End If

In Access 2.0 and later, the Counter/AutoNumber field is updated as soon as you begin inserting a new record, which invalidates the method that was used above with Access version 1.x.

<div class="references_section">