I am creating a data input form where two of the fields are non duplicates indexes (not my key). When entering data on the form, the duplicate status is not tested until the user attempts to move to a new record at which point, they get the generic error "cannot move to the specified record" (if using the next button on the form) or "changes cannot be made... duplicate index value..." if using the built in navigation buttons. Either way it does not inform the user which field is causing the issue, just that they cannot save their record. The users of this form are not familar with access and are easily confused when things like this happen.
I would like to do one of two things.
1) Verify that the field is not a duplicate value before continuing on to the next field in the form and provide them with a friendly and helpful error message
2) Check for duplicate fields before the entire record is saved but inform the user which field caused the problem and provide them with a chance to fix it.
The data table is rather large (200,000 + records) and growing, so I am trying to avoid running a query against the table every time they enter data. They enter many records a day and the speed is essential. At the end of the fiscal year, the data table is moved to a historical table to keep the size down but they still grow large during the year.
They are declared as unique in the table design. The issue is how the user is informed of the attempt to add duplicate values for a field. There are two fields on the form that cannot have duplicate records. It does not inform the user of the duplicates until they attempt to save the record at which time the warning does not specify which field the duplicate attempt came from.
I tried doing that, 2 issues. The form on error event does not trigger with the button I provided to go to the next record (essential because without it the user will have to stop typing and use the mouse to change to next entry). Also, the error trapped still does not say which field caused it, just that duplicate index values would be created.
the only other way I can think of is checking the duplicates. if these two fields are indexed, process wouldn't take that long I believe. which it is what access doing behind the scenes when checking unique indexes.