Your post has gone, unanswered, for over 24 hours, now. I suspect the problem is that very, very, very few of the experts here use Macros, with the possible exception of a specialized one call AutoExec that fires when an Access Database first opens. Macros are simply too inflexible, too difficult to debug/trouble-shoot, and make understanding what is being done difficult. If you're serious about learning how to develop Access Databases, you're really going to have to bite the bullet and learn VBA.
The AutoIncrementing of ID-like numbers, which is what you're talking about, is pretty straightforward, but once again, is best done using VBA.
Also, exactly how would you expect, with an AutoIncremented-number, to avoid the problem of missing numbers? If Record #15 of a series of, say, 30 Records, is deleted, that space between Records #14 and #16 is still going to be missing, regardless of how the number was generated. Using the standard DMax+1 would only help with this is the last Record entered were the one being deleted.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007