Nadda - event Sub_Form_Error() never fires. I assume the error handler in the Click() event took priority over the form event.
VBA code to detect field(s) causing the key violation?
- Thread starter mdlueck
- Start date
Nadda - event Sub_Form_Error() never fires. I assume the error handler in the Click() event took priority over the form event.
1) All of my ADODB database interaction I have placed into a Class within this Access FE database. So the On Error event is at the Form level, whereas the context the code is executing in at the time the exception happens is in the Class code, which has been called by a button on the form.
2) I discovered that it appears if I did not enable On Error handling within each event, at the Form level at least there would be the option to shut off Access's handling of the error as the On Error event provides a Response arg which I could set to
which would disable Access's handling of the error event. I found this information here:
"MS ACCESS TRAINING - Resolving errors with combo boxes"
http://www.moretools.com/lessons/msg_not_in_list.htm
So how would it be possible to use the "Response =" Rx in the context of Class based code? I have already tried adding "Response = acDataErrContinue" into the On Error label within this Function in the Class, and Access complains that the variable is not defined. I have already found references to Access not magically noticing the variable and obeying it on its own when hard coded into places that it is not intended to be supported from. It would seem there is a disconnect then in needing the On Error event which seems to be only offered at Form level in my context of Class level code.
Suggestions?
2) I discovered that it appears if I did not enable On Error handling within each event, at the Form level at least there would be the option to shut off Access's handling of the error as the On Error event provides a Response arg which I could set to
Code:
Response = acDataErrContinuewhich would disable Access's handling of the error event. I found this information here:
"MS ACCESS TRAINING - Resolving errors with combo boxes"
http://www.moretools.com/lessons/msg_not_in_list.htm
So how would it be possible to use the "Response =" Rx in the context of Class based code? I have already tried adding "Response = acDataErrContinue" into the On Error label within this Function in the Class, and Access complains that the variable is not defined. I have already found references to Access not magically noticing the variable and obeying it on its own when hard coded into places that it is not intended to be supported from. It would seem there is a disconnect then in needing the On Error event which seems to be only offered at Form level in my context of Class level code.
Suggestions?
I was able to gracefully handle this error condition by moving the definition of the ADODB RecordSet object into each method instead of relying on one shared / common RecordSet for the duration of the application running. The RecordSet object appears to be toast once a key violation happens to it. With this change, I now successfully get the same key violation message each time I try committing a row which violates an unique index. Woo hoo!
Next up is to add some selects searching for a dupe value, and if found turn the field's red.
In a multi-user environment, even if the odd case happened where the selects came back clean and then the commit fails, now the FE UI stays connected (due to RecordSet objects being always recreated) and the next time the user tried to commit, THEN the fields will turn red.
Good enough duct tape on this issue I think!
Next up is to add some selects searching for a dupe value, and if found turn the field's red.
In a multi-user environment, even if the odd case happened where the selects came back clean and then the commit fails, now the FE UI stays connected (due to RecordSet objects being always recreated) and the next time the user tried to commit, THEN the fields will turn red.
Good enough duct tape on this issue I think!
