AddNew Increments AutoNumber Fields

In my previous article on overriding Access's default arrow key behavior on continuous forms, I wrote that there were still a couple final details to work out, including avoiding unnecessary incrementing of the autonumber ID.

What is this problem exactly?  Honestly, it was a problem I didn't even realize I had until Access MVP Ben Clothier of IT Impact wrote in to alert me to it.  As it turns out, Ben had just barely beaten me to the punch (by about eight years) with his own article about using WithEvents to override Access's arrow key navigation: Maximizing Code Reuse: Multiple Event Handlers.  (Be sure to check out more of Ben's writing at his IT Impact author page.)

In a comment to that article, Ben wrote this describing the problem in detail:

"In a recordset object, you can’t just “move” to a new record. When you issue a AddNew command on form’s recordset, this mimics the action of typing something in the form’s new record placeholder but that creates several side effects; a new autonumber is incremented, the form is now dirty and so forth."

SelTop to the rescue

When Ben emailed me to point out the issue with the autonumber incrementing, he also provided me with the solution he uses instead.  Rather than use the AddNew method of the form's recordset object, he uses the form's SelTop property to move the focus to the new record.

To the user, the SelTop behavior is the same as the AddNew behavior.  Both approaches move the focus to the new record row on the form.  But using SelTop neatly avoids the unintentional incrementing of the autonumber field.  

As Ben points out, the "[n]ew record placeholder at the end of form’s records is entirely a form artifact."  Thus, using the SelTop property of the form avoids making unintended changes to the form's recordset.

Before and after

Here's the existing portion of our code that needs to be replaced:

    Case vbKeyDown
        If Not rs.EOF Then
            rs.MoveNext
            If rs.EOF Then
                If Frm.AllowAdditions Then
                    rs.AddNew
                Else
                    rs.MovePrevious
                End If
            End If
            Ctl.SetFocus
        ElseIf Frm.AllowAdditions Then
            rs.AddNew
        End If

And here's the replacement code:

    Case vbKeyDown
        If Not rs.EOF Then
            rs.MoveNext
            If rs.EOF Then
                If Frm.AllowAdditions Then
                    'Every call to rs.AddNew increments the AutoNumber,
                    '   even if we don't edit the new record;
                    'Setting Frm.SelTop avoids this problem
                    Frm.SelTop = rs.RecordCount + 1
                Else
                    rs.MovePrevious
                End If
            End If
            Ctl.SetFocus
        ElseIf Frm.AllowAdditions Then
            Frm.SelTop = rs.RecordCount + 1
        End If

Thanks, Ben!

Image by Gábor Adonyi from Pixabay