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