?
Solved

vba code breaks on second DAO error within the same routine despite error handling

Posted on 2012-03-14
9
Medium Priority
?
454 Views
Last Modified: 2012-06-22
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:

Sub test2()
    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.Edit
        rs("uniqueText") = "test"
        rs.Update
skip:
        rs.MoveNext
    Loop
   
Exit Sub
errHandler:
    MsgBox (Err.Description)
    GoTo skip

End Sub
0
Comment
Question by:velcrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37723043
what error are you getting?
0
 

Author Comment

by:velcrow
ID: 37723075
the error is the description of how the validation rule was violated
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37723086
what validation?  be more specific..
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:velcrow
ID: 37723144
Any validation / index / key that is violated when the recordset updates, but that is irrelevant to the question.  The problem is not the error but the fact that it is not being handled by the error handler but rather causes execution to break.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37723151
so, what do you want to happen? the codes to continue running ?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37723160
try this

Sub test2()
    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.Edit
        rs("uniqueText") = "test"
        rs.Update
        rs.MoveNext
    Loop
errExit:
Exit Sub
errHandler:
    MsgBox (Err.Description)
    Err.Clear
    Resume errExit
 

End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37723210
or this one


Sub test2()
    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.Edit
        rs("uniqueText") = "test"
        rs.Update

        rs.MoveNext
    Loop
errExit:
Exit Sub
errHandler:
    MsgBox (Err.Description)
    Err.Clear
    Resume Next
 

End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37723222
<The problem is not the error but the fact that it is not being handled by the error handler but rather causes execution to break.>

Like capricorn1, I am not following the logic of your error handler...

I would use capricon1's code syntax and add this as the error handler:

errExit:
    Exit Sub
errHandler:
    If Err.Number = 3022 Then
        MsgBox "No Dupes allowed"   'Or whatever you want to do if this error is encountered...
    Else
        MsgBox (Err.Description)
    End If
    Err.Clear
    Resume errExit

You can split the points if the combination of both of these suggestions solves your issue

JeffCoachman
0
 

Author Comment

by:velcrow
ID: 37723363
capricorn1 and boag2000,

I did not mean to distract you with ‘how to handle the error’, as that was not the problem, and the sample code was only something I quickly threw together so somebody could duplicate the problem.  

The problem was that after the first DAO error, any subsequent DAO errors would not go to the error handling code as specified by ’On Error GoTo errHandler’ but instead would cause and unhandled error and halt execution of the code with a visual basic runtime error dialog.

As it turns out, the solution was very simple and I don’t know how it escaped me for so long.

capricorn1,
your code gave me the solution which was to simply replace ‘goto’ with ‘resume’ in the error handler.   I don’t know why, but apparently ‘goto’ does not have the same effect as ‘resume’ when handling DAO errors.  I guess this wasn’t a 500 point question after all, but I’ll award 500 points to you.  Thanks!


As a follow up question, do either of you know how to make access ignore validation errors thrown by the ‘Before Change’ data macro when running an append query and append all the records anyway, except those that throw an error?

For example, under normal circumstances when running an append query that creates duplicate records based only a primary key, access warns you of the records that will not be appended but will still append all those that it can.  I would like this same behavior even when validation is done through the ‘Before Change’ data macro, but instead it’s all or nothing; if one record violates the ‘Before Change’ event, then none of the records get appended.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question