When the error reappears error handling does not work

error handler vba
vba error handling best practices
vba error handling in loop
vba if error then
vba on error resume next
on error goto not working
vba on error exit sub
vba if error skip

Logics:

  • User. Presses the button;
  • Code . Creates a "Recordset" "rstStud" (students) and "rstGroupStud" (Student Groups);
  • Code . Cycle. Enumerates "rstStud";
    • Code . Add entry to "rstGroupStud";
    • Code . If the record exists, go to the next record in the loop;
    • Code . If the entry is new, add an entry in "rstGroupStud"; Essence: one click of a button - add one unique entry.

Problem. When the cycle passes! StudentName = "Student Name 2" in the line ".Update" I get an error. Error: "Failed to make changes due to duplicate values ​​in index, primary key or relationships. Change the data in one or multiple fields containing duplicate values delete the index or override it by allowing duplicate values ​​and try again. "

In other words, the first time the error handler works normally, and when I re-fire, I get an error.

Question. How to make the code work according to the described logic?

Private Sub btnAddRecord_Click()
    Dim nameStud As String

    Dim rstStud As DAO.Recordset   '
    Dim rstGroupStud As DAO.Recordset '

    Set rstStud = CurrentDb.OpenRecordset("tbl_02_Students", dbOpenSnapshot)  '
    Set rstGroupStud = CurrentDb.OpenRecordset("tbl_03_GruopsStudents", dbOpenDynaset)  '

    ' *** rstStud
    With rstStud
        Do Until .EOF = True
            nameStud = !nameStud

            On Error GoTo errend
            ' *** rstGroupStud
            With rstGroupStud
                .AddNew

                !idGroup = Me.id_GroupFrm
                !nameStud = nameStud
                ' nameStud
                .Update
            End With
            rstGroupStud.Close
            Me.frm_03_GruopsStudents_tbl.Requery

            Exit Sub
errend:
            .MoveNext
        Loop
    End With

    On Error Resume Next
    rstStud.Close
    Set rstStud = Nothing
End Sub

Update_1 File - link

You need to de-tangle the execution paths; normal and error execution states are intertwined, that's why any error beyond the first one can't be handled.

Private Sub btnAddRecord_Click()
    Dim nameStud As String

    Dim rstStud As DAO.Recordset   '
    Dim rstGroupStud As DAO.Recordset '

    Set rstStud = CurrentDb.OpenRecordset("tbl_02_Students", dbOpenSnapshot)  '
    Set rstGroupStud = CurrentDb.OpenRecordset("tbl_03_GruopsStudents", dbOpenDynaset)  '

    ' *** rstStud
    With rstStud
        Do Until .EOF = True
            On Error GoTo ErrHandler
            nameStud = !nameStud

            ' *** rstGroupStud
            With rstGroupStud
                .AddNew

                !idGroup = Me.id_GroupFrm
                !nameStud = nameStud
                ' nameStud
                .Update
            End With
            rstGroupStud.Close
            Me.frm_03_GruopsStudents_tbl.Requery

            Exit Do
TryNext:
            On Error Resume Next
            .MoveNext
            If Err.Number <> 0 Then Exit Do
            On Error GoTo 0
        Loop
    End With

    On Error Resume Next
    rstStud.Close
    Set rstStud = Nothing
    On Error GoTo 0
    Exit Sub

ErrHandler:
    Resume TryNext
End Sub

That way ErrHandler only ever runs in an error state; TryNext runs in the "happy path", and Exit Do breaks out of the loop (but not out of the procedure) so that the cleanup code can run whatever the outcome is.

VBA Error Handling - A Complete Guide, When the error appears you can choose End or Debug. If you select End If we add further error handling it will not work as the error trap has not been cleared. If the error message appears again before you have time to move the mouse to the abort button, try using the keyboard to acknowledge the error <Enter> and then quickly press the abort button with the mouse. You may have to try several times before it works.

Don't do it in VBA. Do it in a query instead.

For example, you could do this way:

Create a query named qryAssignStudentsToGroup:

PARAMETERS id_GroupFrm INT;
INSERT INTO tbl_03_GruopsStudents (idGroup, nameStud)
SELECT id_GroupFrm, nameStud
FROM tbl_02_Students AS s
WHERE NOT EXISTS (
  SELECT NULL
  FROM tbl_03_GruopsStudents AS g
  WHERE s.nameStud = g.nameStud
    AND g.idGroup = id_GroupFrm
);

Then your code behind becomes:

Private Sub btnAddRecord_Click()
  With CurrentDb.QueryDefs("qryAssignStudentsToGroup")
    .Parameters("id_GroupFrm") = Me.id_GroupFrm
    .Execute
  End With
  Me.frm_03_GruopsStudents_tbl.Requery
End Sub

This gives you much less code for same thing, and you eliminate the complexity around error handling simply by filtering out the rows. More importantly, you perform a bulk update once, rather than for each row you touch. This leverages the power of database engine. It's convenient to do things in a loop with a DAO.Recordset but that's row-by-agonizing-row programming. You really want to get in mindset of set-based programming.

Errors and Error Handling, To prevent this error, all variables should be properly declared beforehand. progress working through a coding problem when all of a sudden an error like this gets thrown at You might recognize errors as the scary red text that appears … Click on the image above to receive the free eBook and other content not available on the blog.

You must call Err.Clear to reset the error state

errend:
    Err.Clear
    .MoveNext

I would call Me.frm_03_GruopsStudents_tbl.Requery after the loop. There is not point in requerying the form all the time.

But jumping to another regular code part instead of going to an error handler is not the usual way of handling errors. To address the possibility to handle errors on MoveNext, change the code like this:

Private Sub btnAddRecord_Click()
    Dim nameStud As String

    Dim rstStud As DAO.Recordset
    Dim rstGroupStud As DAO.Recordset

    Set rstStud = CurrentDb.OpenRecordset("tbl_02_Students", dbOpenSnapshot)
    Set rstGroupStud = CurrentDb.OpenRecordset("tbl_03_GruopsStudents", dbOpenDynaset)

    ' *** rstStud
    With rstStud
        Do Until .EOF = True
            nameStud = !nameStud

            On Error GoTo UpdateError
            ' *** rstGroupStud
            With rstGroupStud
                .AddNew

                !idGroup = Me.id_GroupFrm
                !nameStud = nameStud
                ' nameStud
                .Update
            End With
            rstGroupStud.Close
            Me.frm_03_GruopsStudents_tbl.Requery

            Exit Sub
continue_loop:
            On Error GoTo MoveNextError
            .MoveNext
        Loop
    End With

CleanUp:
    On Error Resume Next
    rstStud.Close
    Set rstStud = Nothing
    Exit Sub

UpdateError:
    Resume continue_loop

MoveNextError:
    MsgBox Err.Description
    Resume CleanUp
End Sub

This pattern is extensible. You can add as many error handlers as required.

set_error_handler - Manual, If the error-handler function returns, script execution will continue with the next statement This error code is not included in error_reporting, so let it fall It appears that errcontext is (in effect if not literally) created by taking $GLOBALS and� The problem of this error handling pattern is that there is no enforcement from a compiler. It is up to you on what and how your function returns an error. You can put an error struct in any

Error Handling, does not work to handle errors. You should also be familiar with the four main ways to deliver an error in Node.js: throw the error (making it an exception). pass � But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I’m going to use the terms sub, function and procedure interchangeably.

Error Handlers, Mule throws a messaging error (a Mule error) whenever a problem occurs within description appears in the browser for two reasons: The default error handler� About the contributors. Link provided by the UtterAccess community.. Handling Access Errors with VBA; UtterAccess is the premier Microsoft Access wiki and help forum. See also

Web API Error Handling: How To Make Debugging Easier, Understand the problem of error handling from two angles: the what and the CODE: Even though the HTTP code comes back on the header,� This code is not nice, not clean, not elegant, and still wrong: like the previous version, it does not remove dst when io.Copy or w.Close fails. There is a plausible argument that at least a visible check could prompt an attentive reader to wonder about the appropriate error-handling response at that point in the code.

Comments
  • From the top screenshot, it looks like you have a field named "PrimaryKey", but it's the only one that isn't part of the composite primary key.
  • @Comintern Excuse me. I do not understand what needs to be done to solve my problem? Could you formulate more precisely what to do? Thank.
  • Move your error-handling code out of the "happy path" - make it a completely separate label that only ever runs in an error state. The problem you're having is that when an error occurs you're not Resume-ing, so the next loop iteration is still running in an error state and then any subsequent error can't be handled.
  • Without seeing a dependencies diagram, it looks like the idGroup and NameStud fields are forming a composite primary key instead of both of them being foreign keys to their respective tables. That's a M-M joining table, correct? The point being that you shouldn't approach this from the standpoint of how to "fix" the error handling - you fix it by not attempting to write duplicate keys.
  • FYI nobody in their right minds is going to download a macro-enabled file from a random stranger on the Internet - that dropbox link isn't useful.
  • I never encountered this case. But then you would have to implement two different error handlers.Put a On Error Goto someOtherPlace before .MoveNext. You cannot continue anyway in this case and would have to leave the Sub
  • @Comintern: I updated my answer with a better structured error handling.