Access over writes an existing record.

[gg80]

This is an impossible problem, but thought that I would post it in case someone else has run into it



I have a db file, split, with all tables in a server. One linked computer uses A2003, and three use A2007. One of the tables is for customers. When we get a new customer, personal data is entered into a form sourced only to that table. ID is an autonumber.

Somehow, a customer’s data got substituted by a later customer entry (next or same day). I know that it was substituted because an older backup file has the correct name for that same id.

Since the id was the same, various reports and forms now showed the wrong name for related data. The users claims that they didn’t overwrite the older entry. There is just no reason to do so. Once the users enters someone, they only modify things if spelling is wrong, the phone no. changes etc.

This may be relevant: There are two id autonumbers “missing”after the problem entry. That is, the id number for problem entry is 20977,then next entry skips to 20979. The user claims that he didn’t do any deletions-no reason to-never has.

The system has been in operation for three years. I had preserved a lot of old the tables while doing mods , so was able to check back to see if there were any other times that the same customer id number was shared by two customers for files saved at different dates. Found none.
Somehow, the new customer data overwrote the old customer data this one time.

What I am describing here can’t happen, but----. If anyone else has an idea of what is going on, love to hear about it.

[ItsMe]

I have not seen anything like that. The problems I have seen are usually an entire row within one table becomes corrupt. The row gets deleted and replaced using a query and data from a backup DB.

I have not been able to delete an Autonumber before. I have only been able to corrupt an entire Row/Record.

Not sure what I would do in your case. Depending on the size of the DB and hours of operations, I might consider migrating data to new tables and DB. Also, should only be using MDB file as backend if any users are running version older than 2007.

[Micron]

Since you have proof of prior records, it is probably not the absence of record locks as well as not confirming record changes (Options > Advanced). I'd venture to say that when some condition occurs that does not present itself very often, a glitch in code combined with an unanticipated user method is over-writing or deleting records. This could be next to impossible to uncover, but I'd be inclined to approach it from that perspective. I would watch users perform the tasks, looking for a user method that you did not anticipate and might recognize as a potential cause of the problem. If you have user tracking of some sort, you probably have an idea of who to observe because they are associated with the changes. If nothing arises, is there someone else who could troubleshoot the code after telling them what to look for?

[gg80]

Thank you both for looking at this. Since the customer was overwritten by the very next new customer entry (since my first posting, I have found that the entries were several days apart), it does look as if some unanticipated user method could be involved. I have tried to make it happen again, but no luck.

Just to avoid the possibility of some internal corruption, I have recreated the table and form scratch. The code for new entries is pretty simple. If you have time, would appreciate a quick look. I will post this to other kind respondent as not sure you get to see other replies.

Private Sub NewCust_Click()


If Me.NewC = 1 Then 'This is 2nd click. If gets clicked-a second time, ignore or will make new useless record
' if NewC=1 and user clicks existing customeer button, will ask user if wants to save or delete this.
Exit Sub
End If


Me.HideC.Visible = False 'remove cover
Me.CustBox.Visible = False 'remove existing customer pulldown to save confusion.
'
DoCmd.GoToRecord , , acNewRec
Me.DateEnter = Now()
Me.Lname = "Unk" ' I'in case don't enter a lname
Me.NewC = 1 'signals that new customer has been clicked
Form.Refresh


End Sub

Since you have proof of prior records, it is probably not the absence of record locks as well as not confirming record changes (Options > Advanced). I'd venture to say that when some condition occurs that does not present itself very often, a glitch in code combined with an unanticipated user method is over-writing or deleting records. This could be next to impossible to uncover, but I'd be inclined to approach it from that perspective. I would watch users perform the tasks, looking for a user method that you did not anticipate and might recognize as a potential cause of the problem. If you have user tracking of some sort, you probably have an idea of who to observe because they are associated with the changes. If nothing arises, is there someone else who could troubleshoot the code after telling them what to look for?

[gg80]

Thank you both for looking at this. Since the customer was overwritten by the very next new customer entry (since my first posting, I have found that the entries were several days apart), it does look as if some unanticipated user method could be involved. I have tried to make it happen again, but no luck.

Just to avoid the possibility of some internal corruption, I have recreated the table and form scratch. The code for new entries is pretty simple. If you have time, would appreciate a quick look. I will post this to other kind respondent as not sure you get to see other replies.

Private Sub NewCust_Click()


If Me.NewC = 1 Then 'This is 2nd click. If gets clicked-a second time, ignore or will make new useless record
' if NewC=1 and user clicks existing customeer button, will ask user if wants to save or delete this.
Exit Sub
End If


Me.HideC.Visible = False 'remove cover
Me.CustBox.Visible = False 'remove existing customer pulldown to save confusion.
'
DoCmd.GoToRecord , , acNewRec
Me.DateEnter = Now()
Me.Lname = "Unk" ' I'in case don't enter a lname
Me.NewC = 1 'signals that new customer has been clicked
Form.Refresh


End Sub

I have not seen anything like that. The problems I have seen are usually an entire row within one table becomes corrupt. The row gets deleted and replaced using a query and data from a backup DB.

I have not been able to delete an Autonumber before. I have only been able to corrupt an entire Row/Record.

Not sure what I would do in your case. Depending on the size of the DB and hours of operations, I might consider migrating data to new tables and DB. Also, should only be using MDB file as backend if any users are running version older than 2007.

[Micron]

Don't know what is going on before this click, but here's what jumps out. If you are incrementing some value and detecting that it is equal to 1 because it is the second click, then exiting the sub, what happens if user clicks a third time? Your trap will not work if 1 becomes 2. If any form fields were edited, the changes will be committed because you are leaving that record. Perhaps someone starts editing and realizes they should not be on that record, then commits the changes in an attempt to move on.

[gg80]

Thanks for input. The control isn't incremented, it is set to =1, so will always be 1 or null. I am becoming more and more convinced that it was some operator error.

Don't know what is going on before this click, but here's what jumps out. If you are incrementing some value and detecting that it is equal to 1 because it is the second click, then exiting the sub, what happens if user clicks a third time? Your trap will not work if 1 becomes 2. If any form fields were edited, the changes will be committed because you are leaving that record. Perhaps someone starts editing and realizes they should not be on that record, then commits the changes in an attempt to move on.

[ssanfu]

One thing struck me. Referring to the tables is for customers, you said:

ID is an autonumber.

and

That is, the id number for problem entry is 20977,then next entry skips to 20979.

If you expect that the ID number is going to be gap-less and sequential, you are mistaken. The autonumber type field is only guaranteed to be unique.
Autonumber type fields should never be used as meaningful data.

See
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Also
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[gg80]

Thanks much. I read the articles and they gave me better understanding of something that I hadn't thought about. Though, I have never seen autonumbers be other than increasing with new entries. I looked back on some of my old stuff and do see some unexplained gaps, but always sequential. Have to admit that I need to read it again, particularly references to secondary indices. It seems that learning Access, for me, is a never ending line of surprise discoveries. Thanks again.