Duplicate field occurring with autonumber

[thebourboncowboy]

Long time listener, first time caller...



I have a database that I wrote for a small non-profit store...has been working perfectly for 14 years.

In the past month this problem has occurred twice, and I can't for the life of me figure out HOW this could possibly happen.

I have a table of transactions. TransactionID is the primary key. It's an autonumber, indexed, no duplicates.

twice in the last month we have discovered that a transactionID is duplicated in the table.

I realize that I don't have a ton of details...though I'd be happy to answer any questions you guys can think of...I'm so confounded by this problem that I'm not even really sure where to start.

[Bulzie]

That is odd. Is it an Access table or SQL linked table? If you put that table in a query and search for that duplicate ID, does it pull up 2 records or just 1? What happens if you link it to another table, do both values link so give you dup records?

[JoeM]

Have you seen it (the duplicate) for yourself?
Did you confirm that the field is still set up as a Primary Key Autonumber field?
How many records do you have?
What is the highest value you have in this field in the table?

Note that Autonumbers are typically set up to be Long Integers. Long Integers have limitations (-2,147,483,648 to +2,147,483,647).
See: https://support.office.com/en-us/art...2-36b93f966a33
Any chance you have bumped up against this maximum value?

Also, make sure that you are viewing the table directly, and not in some query where the table is joined to another table (you could be fine and just have a one-to-many join going on).

[thebourboncowboy]

Currently, the autonumber is about 210,000 so we aren't in danger of hitting the long int max. Each year we dumb the transactions into a separate history database...to keep the active database nimble... so there are only 10-15k records in that table now.

It's an access table, not a sql table and I have looked at the table myself and confirmed that the duplicate autonumber is there...and if I run a show duplicates query on the table, they both appear.

If I run a compact/repair, it spits out errors because it finds those duplicates.

Here is more info on how I actaully use/create records in that table.

first, I use a front-end app database that is just forms/queries/reports... and it links to the tables in the backend acces database. Both are 2013 access databases.
I do this because we have 2 check out stations at the store...and it allows people to check out at the same time.

I create a new record in the table when the volunteer hits the "check out" button... it runs vba code to
open the table (dbs.OpenRecordset)
addnew
transactiondate = now()
customerid = (i get the customer ID from a variable)
.update
I close the recordset
then I open the transaction form to that specific transactionID

Again, this has worked perfectly for 14 years... it's only in the last month that this error has been occurring.

also of note, the vba code does it's job...and actually gets a new autonumber (not a duplicate)... I know this because I have a table that has all the transaction items in it... your basic one-to-many... I have a transaction table that just has the transactionID, the date, the customerID, and a few other transaction-specific details. Then I have a transactionitem table that has all the items they purchased... that has it's own autonumber, then the transactionID that points back to the transaction table, then the item number, and how many they bought.

so I can open that transactionitem table and find the items that were bought on the "missing" transactionID.

If I look at the transaction table I will see:
autonumbertransactionID
208501
208503
208503

that 208503 should never ever ever be repeated... and 208502 is missing...but if I look in the transactionitems table, 208502 is there and I can manually reassemble the transaction. The fact that the correct transactionID is in the transactionitems tells me that the transaction completed correctly... a receipt printed...with the correct data... and the customer went on their merry way.

Does that make sense? Unfortunately the store is run by volunteers and when I ask, "did anything weird happen like the app crash or pop up an error or anything" they all assert no... nothing happened. And since it's a small non-profit store with loyal customers, they were able to call one of the customers that had a missing transaction...and they brought their receipt in...and sure enough it's correct. has the correct items on it, the correct transactionID. everything is correct.

but at some point before the end of the day that autonumber transactionID gets replaced with a duplicate transactionID.

and... the store continues to run for the rest of the day with no problems... they continue to create new transactions and sell items...and everything after it works fine. They only figure out something is wrong when they run reports at the end of the day.


what could possibly cause this? or at this point I'm ok never knowing...just what can I do to prevent it from ever occurring again?

[Micron]

Definitely make sure of the points suggested, but know that while rare, this is not impossible.
Someone had this issue a couple of weeks ago here and in researching it, I found this http://allenbrowne.com/ser-40.html
Note that the page was last updated in 2015, and although at first it may read like it only applies to versions older than yours, 2013 is mentioned in the article.