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

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

The status of the AutoNumber field (or Counter field in version 2.0) cannot be used 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, 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>])

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. This property can be checked in the BeforeUpdate 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. 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)

   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."