vba code breaks on second DAO error within the same routine despite error handling
Posted on 2012-03-14
I'm looping through a dao Recordset and making updates to the records. Some updates violate keys and/or data validation rules causing an error to occur at the Update method of the recordset. The routine does have appropriate error handling and the first time a data validation error occurs it works great, but the second time the error handling gets completely ignored and the execution breaks and displays the error in the default VBA runtime error dialog.
Here is some sample code to duplicate the error. Create a 'Table1' with one field 'uniqueText' and add three records to the table and then try to update the records with:
On Error GoTo errHandler
Dim db As DAO.Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("Table1")
Do While Not rs.EOF
rs("uniqueText") = "test"