Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Access 2007 losing records when users hit ESC too many times

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
J.R. Sitman
Asked:
J.R. Sitman
  • 3
  • 2
1 Solution
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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
 
J.R. SitmanAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
J.R. SitmanAuthor Commented:
Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now