Blank records question

I am using "DoCmd.RunCommand acCmdUndo"  on a button that closes my form to prevent blank or incomplete records from being added.  The only problem I have is my UID is AutoNumbered, and everytime this button is pressed a UID gets deleted (ex. if users start to enter a record, then press the "Close Form", blank record does not get saved - GOOD - but the particular UID that would have been assigned to that record gets deleted - BAD - which throws the sequence off.  If, for instance, user completes record 23, starts to enter record 24 but does not complete it and instead closes the form, record 24 dissappears completely and next time user will start at record 25.)

Is there any particular way to combat this problem?  

Thanks in advance.  Please let me know if this needs further explaining.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hey iptrader!

  This is a natural function of the AutoNumber and Undo,  once used and trashed, its gone...
  The only cure for this is to compact the database that the table is located in IMMEDIATELY after the autonumber gets trashed.  Access would "recycle" the numbers, re-beginning at the highest number in the current recordset.
  (this is normally NOT thought of as an acceptable solution,...)

  You could use an unbound form to put data into and only commit this data to the table if the user confirms that it is good to go.
  This would prevent the Creation/Trashing of the autonumbers as only good records would be put into the table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
As you have found out, Autonumber really was never intended to be seen by or have much value for end users but is useful as an artificial PK (please don't get started Jack started :-). If you really need a sequential number you are better off writing a custom function to generate it rather than trying to use Autonumber for this purpose.

An autonumber field should *never* have a meaning, it should only be used to uniquely identify a record. Your problem is just an example of why an autonumber field should not have a meaning.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

iptraderAuthor Commented:
The UID is not seen by the users, it is only used for my purposes.  It's more of an annoyance than anything else.

Sorry Steve did not see your comment before I posted but as you can see I fully agree with your comment. BTW JDettman made an excellent function for this purpose, see:
jadedataMS Access Systems CreatorCommented:
I'd say let it be then...  The element has no value other than possibly retrieving a specific record later using this id to find it.
iptraderAuthor Commented:
Thank you for your comments!  Jack, I think I'll take your suggestion, it is becoming more trouble than it's worth.

Best to all,

jadedataMS Access Systems CreatorCommented:
Thanx for the question!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.