Solved

Access 2007 losing records when users hit ESC too many times

Posted on 2012-04-10
5
299 Views
Last Modified: 2012-04-13
In our Access 2007 database if a user is creating a new record and they make a mistake and use the "esc" key to back out of the mistake, and if they go too far, it backs all the way out of the record and we lose that record #.  All our animal impounds have numbers so if they were on 12-0100 and did the above steps, 12-0100 no longer exists in the table.
Is there a fix or way to stop this from happening?  Since 9/1/11, we've lost 14 records.
0
Comment
Question by:jrsitman
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37830082
Yes, pressing escape a few times actually cancels the record.

<so if they were on 12-0100 and did the above steps, 12-0100 no longer exists in the table.>

Sequential numbers (Primary Key Autonumbers, ...etc) are mainly used to keep the records unique.
The "Sequence" is no big deal.

If the record was created, then "escaped" then yes, there is a "hole" in your numbering, ...but so what?
If the record was never "saved", then it never really existed anyway...

When you get a SSN, is it sequential? , ...No

This happens all the time, it is no big deal...
Suppose you entered a record two minutes ago then the animal dies.
You still will be missing that number if you delete the record for that animal .

If your question is how to "Prevent" people from cancelling out of a record, then what happens if they really do need to cancel out of a record?

This all being said you can save the record immediately after the enter anything in any control for this new record .
But this code must be put on the After Update event for each control...
    DoCmd.RunCommand acCmdSaveRecord
Not worth the effort...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37830101
In other words, what is your real question here...

    "How to prevent records form being cancelled out of? (or deleted)?"
Or
    "Is there a problem if I have holes in my numbering system?"

JeffCoachman
0
 

Author Comment

by:jrsitman
ID: 37830151
The reason it is important to us is the statistics I create show that we received 1000 animals, however our last impound number is 12-1014.  Hence there are 14 more impound numbers than actual animals received.

Yes the question is "How to prevent records frrom being cancelled out of?"
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 37830731
1. Again, you are putting to much stock in the sequential number...

<the statistics I create show that we received 1000 animals>
How are you getting this statistic? it should be a simple count of the records in the table...
<however our last impound number is 12-1014. >
So what?
The number of records is the actual count, not the sequential number...
Nobody should be looking at the sequential number as the count, as you stated, you already have this statistic...They should be referring to that figure.

2. How is this sequential number being incremented?
I see that it seems to be preceded by the year...

3. Is this sequential number the primary key?

4. Again, what happens if a record really needs to be cancelled (a mistake)
Then what?

5. What happens if a record needs to be deleted
Then what?

What is your experience with database systems?
I have never seen a system where a field value was use to give a "Count" of the number of records...?

Sure, someone can devise a system that counts each record as it is created, them assign the next number, but again what about records the really need to be cancelled out of, or deleted?
You really can't "re-order" the list, because records would have to be "re-assigned" a new number (to replace the deleted record).
You can do this with a query, but here again, deleted records will shift the numbering and records will have different numbers than what they had before...

Any system I can think of to do this will have at least 3 situations where the count will be "Wrong".

If you need to track the number of Animals for a certain year, you can use a query.
Presuming your table is set up similar to this:
Table Name: tblAnimals
Fields:
aID (Primary Key, Autonumber)
aName
aBreed
aSex
aColor
aDOB
aDateEntered
...etc

The query would look similar to this:
SELECT Count(alD) AS CountOfAnimals, First(Format([aDateEntered],"yy")) AS TheYear
FROM tblOrders
HAVING Year([aDateEntered])=2012;

This query can be set to run at any time from a button or hotkey.
It can be modifed to let the user specify the:Year, Breed, Age, Color , ..etc
(This is something your sequential number cannot do)
So you can leave you autoincrementing number as is. (Just use it for display purposes, Like a SSN in the USA)
Educate users (Or whoever assumed that a field value should be used to "Count" the records) that if they want to know the count, run the query.

But don't take my word on any of this...
Post a new related question of:
    "Should I insist that an auto-incrementing field value always display an accurate count of records?"

Some final notes:
You can even create a query to display your "Key" value in the same format:
SELECT *,Format([aDateEntered],"yy") & "-" & aID AS Key2
FROM tblAnimals


But to answer your main question here:
<"How to prevent records from being cancelled out of?">
(Without doing something complex,) ...You can't

JeffCoachman
0
 

Author Closing Comment

by:jrsitman
ID: 37845249
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now