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

ID: Q210411

The information in this article applies to:

Microsoft Access 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

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.

REFERENCES

For more information about the NewRecord property, click Microsoft Access Help on the Help menu, type "NewRecord Property" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the OldValue property, click Microsoft Access Help on the Help menu, type "OldValue Property" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: forms prb

Keywords : kbusage kbdta FmsHowto
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb