Access error message

0
votes
The changes you have requested to the table were not successful because
they would create duplicate values....

There must be a hundred posts here dealing with this message, but the one thing they all seem to be missing in response is a way to troubleshoot it. I have a form with a dozen tabs/pages. Each page is basically another table in the db. Some pages have sub forms. When I get this error message, how do I know which table is having the problem, so I can troubleshoot? When navigating from record to record, aren't all tables that have referential integrity enabled potentially updated? So the problem isn't necessarily the form/tab/page that I am looking at. It could be any table. I understand what the message is saying. I know about duplicate values and how indexes work. But I don't know exactly what caused this message. I would like to see the INSERT/UPDATE that was attempted. How do I get that?

1
ms-access
Does the error not occur when you attempt to add a record to a form or subform? Does your code contain any SQL statements? Records are not automatically updated by referential integrity, you must add a record in some way. - Fionnuala
Right now I am getting the error just navigating from record to record. Most forms have onCurrent methods that set initial values for fields, and it looks like those can sometimes cause problems. I have the BeforeInsert and BeforeUpdate methods added, and I see that they are called just by navigating from record to record. Is there a way to see what the query is in those methods? And, yes, I do have SQL in some of my code. - MrGibbage
For the most part, I would say setting values in the On Current event is not a good idea. BeforeInsert and BeforeUpdate should not be called by navigating in a form constructed in the normal way. I suggest you get rid of the code that sets values and use the Default Values property or only set these values when the user chooses to add a record. It sounds to me that you may have several of your forms set to Add mode. - Fionnuala
I would generally not use more that one or two subforms, to improve speed and simplicity. Subfroms can be reused. A tab without a subform may be just an additional complication. - Fionnuala
It looks like setting values in the onCurrent is a bad idea, as you suggested. I will need to rethink that. If you reply again as an answer, I will give you credit for the help. - MrGibbage

1 Answers

0
votes

For the most part, I would say setting values in the On Current event is not a good idea. BeforeInsert and BeforeUpdate should not be called by navigating in a form constructed in the normal way. I suggest you get rid of the code that sets values and use the Default Values property or only set these values when the user chooses to add a record.