Error handling before running a While loop?

[MikeN]

I have 4 forms that use a similar error handling to prevent duplicate entries (see first code). Three of the four forms are working as hoped but the fourth isn't. The three that work are identical function-wise but the fourth differs. The AfterUpdate() code in the 3 working forms is simple (i.e., it only makes the subform and a couple option buttons visible) if the On Form Error() event doesn't fire. The fourth form has a While loop attached to an AfterUpdate() - the While loop must stay because it semi-populates the subform and associated table. So, the problem that I'm running into is that the While loop runs, thus tries creating a record that potentially has a duplicate (Error 3022) record. I tried to incorporate the error handling directly into the AfterUpdate() for this form (see second code below) but it isn't working. Instead, it gives me the default access error code (versus what I'm wanting it to do) and sends me to debugging. Essentially, I want it to check for the duplicate errors, undo the form and setfocus back to the first combobox if there is a duplicate transect number trying to be created.



I'm not well versed in VBA but I've tried to use Exit Sub in the On Error GoTo Error which doesn't seem to run the While loop but doesn't undo the form and SetFocus back to the beginning, instead it just goes to the next tabstop item.

Normal Form_Error that works in the three forms

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 Const conErrDuplicateTransect = 3022


    Select Case DataErr
        Case conErrDuplicateTransect 'duplicate transect within a trip
            MsgBox "Transect " & TransectNum & " has already been entered." & vbCr & "Please check transect number and try again.", vbInformation, "Duplicate Transect Number"
            Me.Undo
            Me.frm_BeltSurveyData_Subform.Visible = False
            Me.Notes.Visible = False
            Me.TransectNum.SetFocus
            Response = acDataErrContinue
    End Select
End Sub

Attempts to integrate error trapping into AfterUpdate using On Error GoTo

Private Sub Observers_AfterUpdate()
    On Error GoTo Error 'error duplicate records
    
    If Me.NewRecord Then
        DoCmd.RunCommand acCmdSaveRecord 'create record in tbl_LineSurvey


    Dim x As Double 'add Transect Point 0.5 - 25.0, and LineSurveyID to tbl_LineSurveyData
        x = 0.5
        While x <= 25
        CurrentDb.Execute "INSERT INTO [tbl_LineSurveyData]([LineSurveyID], [TransectPoint]) " & _
         "VALUES (" & [Forms]![frm_LineSurvey]![LineSurveyID] & "," & x & ")", dbFailOnError
        x = x + 0.5
        Wend
    Me.frm_LineSurveyData_subform.Requery  'reload the subform to dislpay populated transect point number
    Me.frm_LineSurveyData_subform.Visible = True 'make the subform visible
    Me.Notes.Visible = True 'make notes field visible
    Me.newLineTransect.Visible = True 'make Add New Transect Line button visible
 End If




Error:
    Dim DataErr As Integer
    Const conErrDuplicateTransect = 3022


    Select Case DataErr
        Case conErrDuplicateTransect 'duplicate transect within a trip
            MsgBox "Transect " & TransectNum & " has already been entered." & vbCr & "Please check transect number and try again.", vbInformation, "Duplicate Transect Number"
            Me.Undo
            Me.frm_LineSurveyData_subform.Visible = False
            Me.Notes.Visible = False
            Me.TransectNum.SetFocus
         End Select
        Exit Sub
End Sub

[MikeN]

Alright, after sitting on it a little more, I think I might have figured it out. It appears to be working as I hoped but I'd appreciate if anyone with more knowledge can take a quick glance over it to make sure there isn't something missing that might be going on behind the scene that might cause 'hidden' problems down the road. Thanks ahead of time.

Edit: Dang it! It only works if I keep the original OnForm Error() event in the Class Objects VBA too. I tried commenting out the OnForm Error() event since I attempted to integrate the handling into the AfterUpdate() event and it gives me the default access message of "...duplicates being created...". Any thoughts on why that might be? Do you always have to keep the OnForm Error () event even if you're integrating an On Error statement in another block of Sub code?

Private Sub Observers_AfterUpdate()

On Error Resume Next
    Select Case Err.Number
        Case 0 'no error -- continue with normal procedure
            If Me.NewRecord Then
                DoCmd.RunCommand acCmdSaveRecord 'create record in tbl_LineSurvey


            Dim x As Double 'add Transect Point 0.5 - 25.0, and LineSurveyID to tbl_LineSurveyData
                x = 0.5
                While x <= 25
                CurrentDb.Execute "INSERT INTO [tbl_LineSurveyData]([LineSurveyID], [TransectPoint]) " & _
                "VALUES (" & [Forms]![frm_LineSurvey]![LineSurveyID] & "," & x & ")", dbFailOnError
                x = x + 0.5
            Wend
            Me.frm_LineSurveyData_subform.Requery  'reload the subform to dislpay populated transect point number
            Me.frm_LineSurveyData_subform.Visible = True 'make the subform visible
            Me.Notes.Visible = True 'make notes field visible
            Me.newLineTransect.Visible = True 'make Add New Transect Line button visible
            End If
        Case 3022 'duplicate transect within a trip error
            MsgBox "Transect " & TransectNum & " has already been entered." & vbCr & "Please check transect number and try again.", vbInformation, "Duplicate Transect Number"
            Me.Undo
            Me.frm_LineSurveyData_subform.Visible = False
            Me.Notes.Visible = False
            Me.TransectNum.SetFocus
            Exit Sub
         End Select
End Sub

[nick404]

I think what your problem originally was, was that you did not correctly call your form's error() event.

What I mean is, you have your Observers_AfterUpdate() event, and a Form_Error() event. If an error occurs in your AfterUpdate() you want to run the Error() event to catch any errors, specifically err code 3022. However, you said it wasn't working. (For future, it's best to let us know what you mean by 'not working' (i.e. what is access saying is wrong, wrong event occurring, does nothing, error codes, etc.)). You have to call the Error() sub in an error handler from your AfterUpdate event, and correctly pass the error's number and a response variable to the sub. Here is what I mean:

Private Sub Observers_AfterUpdate()
On Error GoTo errorProc0
    
If Me.NewRecord Then
   DoCmd.RunCommand acCmdSaveRecord 'create record in tbl_LineSurvey
   Dim x As Double 'add Transect Point 0.5 - 25.0, and LineSurveyID to tbl_LineSurveyData
   x = 0.5
   While x <= 25
       CurrentDb.Execute "INSERT INTO [tbl_LineSurveyData]([LineSurveyID], [TransectPoint]) " & _
       "VALUES (" & [Forms]![frm_LineSurvey]![LineSurveyID] & "," & x & ")", dbFailOnError
       x = x + 0.5
   Wend
   Me.frm_LineSurveyData_subform.Requery  'reload the subform to dislpay populated transect point number
   Me.frm_LineSurveyData_subform.Visible = True 'make the subform visible
   Me.Notes.Visible = True 'make notes field visible
   Me.newLineTransect.Visible = True 'make Add New Transect Line button visible
End If

errorProc0: 'this is where I will call the forms error event
   Form_Error Err.Number, acDataErrContinue 'must send an error number and a response to the method since the method signature calls for it.
End Sub

'errorProc0 will then call this in event of an error.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
   Select Case DataErr
      Case 0
          Exit Sub
      Case 3022 'duplicate transect within a trip
          MsgBox "Transect " & TransectNum & " has already been entered." & vbCr & "Please check transect number and try again.", vbInformation, "Duplicate Transect                     Number"
          Me.Undo
          Me.frm_BeltSurveyData_Subform.Visible = False
          Me.Notes.Visible = False
          Me.TransectNum.SetFocus
          'don't need Response = acDataErrContinue because we set the Reponse variable that in the method call.
      Case Else
          MsgBox "Error " & Err.Number & " has occurred:  " & Err.Description, vbOk, "MS Access Error"
   End Select
End Sub

I usually give a more custom & user friendly error message for the remaining possible errors (the Case Else section) so that more unfamiliar users don't freak out when they get an error message. The code that you have found as a solution, I don't see having any problems, it is something I haven't seen before (putting normal function in the Error 0 case), but I don't see why it wouldn't work... Any questions please ask!

[MikeN]

Thanks Nick! That make sense! I didn't know you could send one Sub to another like you did in errorProc0 which is why I was trying to 'integrate' it into the same Sub. That's some great info! I'll give that a shot in a little bit and report back if I have questions and/or mark this as solved.

Can you give me a simple example of what you mean when you say that you usually give a more custom & user friendly error message? Are you saying something along these lines: MsgBox "An unexpected error has occurred...Error " & Err.Number & "....? Versus the default error messages that access throws?
Thanks again for the help!

I think what your problem originally was, was that you did not correctly call your form's error() event.

What I mean is, you have your Observers_AfterUpdate() event, and a Form_Error() event. If an error occurs in your AfterUpdate() you want to run the Error() event to catch any errors, specifically err code 3022. However, you said it wasn't working. (For future, it's best to let us know what you mean by 'not working' (i.e. what is access saying is wrong, wrong event occurring, does nothing, error codes, etc.)). You have to call the Error() sub in an error handler from your AfterUpdate event, and correctly pass the error's number and a response variable to the sub. Here is what I mean:

I usually give a more custom & user friendly error message for the remaining possible errors (the Case Else section) so that more unfamiliar users don't freak out when they get an error message. The code that you have found as a solution, I don't see having any problems, it is something I haven't seen before (putting normal function in the Error 0 case), but I don't see why it wouldn't work... Any questions please ask!

[nick404]

Yep, you can call other subs from a sub. You can also create functions that perform specific tasks and then call them from one or more subs as well. Can be quite helpful if you need to perform a specific task many times.

Can you give me a simple example of what you mean when you say that you usually give a more custom & user friendly error message? Are you saying something along these lines: MsgBox "An unexpected error has occurred...Error " & Err.Number & "....? Versus the default error messages that access throws?

So normally when Access throws an error its like "Run time Error #### could not blah blah" and typically isn't super helpful and inexperienced users fret when "error" messages pop up. You have a custom error message for adding a duplicate record, and when I error handle I will usually have a few custom error messages like that as well. For the rest of the errors though I will usually just have a slightly modified message. It usually says something like "Unexpected error <number> has occurred: <errors description>. Please check all data fields are filled in correctly." <- I typically have that last sentence in since I have found most of my user errors arise from having incorrectly filled data fields.

Glad to help!
Nick

[MikeN]

Thanks Nick. Worked great!