Solved

Blank records question

Posted on 2003-11-18
8
280 Views
Last Modified: 2012-08-14
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.

IPT
0
Comment
Question by:iptrader
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 100 total points
ID: 9773566
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.
 
Questions?
 
regards
Jack
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 100 total points
ID: 9773606
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.

Steve
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9773619
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

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

IPT
0
 
LVL 23

Assisted Solution

by:heer2351
heer2351 earned 100 total points
ID: 9773658
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:

http://www.experts-exchange.com/Databases/MS_Access/Q_20092292.html
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9773670
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.
0
 

Author Comment

by:iptrader
ID: 9773735
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,

IPT
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9773775
Thanx for the question!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question