Error Handling Questions

[Aaron5714]

I am developing my first multi-user application and would like to implement some basic error handling. Here is the code that I am adding to every proceedure:

Private Const ModName As String = "module name hard-coded here"

Private Sub ProceedureName()
On Error GoTo HandleError:

... normal proceedure code

ExitHere:
Exit Sub
HandleError:
MsgBox "An unexpected error occurred. If this problem persists please contact the system administrator and reference the following information:" _ 
& vbNewLine & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Description: " & Err.Description & vbNewLine & "Module: " & ModName, _
vbCritical, "VBA Error"
Resume ExitHere
End Sub

I have a few questions:

1. Is the code above sufficient for basic error handling, or should I be adding anything else?
2. In addition to adding this code to each procedure, should I also add error handling code to the "On Error" event for every form and report in my database?
3. Rather than having the code that generates my error message duplicated in every procedure, would it be a better idea to create a standard module with a public procedure that would generate this message, and then call that procedure when needed. In the future if I want to test for specific error numbers and take certain actions, it seems like it would be easier update one standard module rather than copying the same code to every procedure in my database. I'm thinking that the end of each procedure in my database would look something like this:

HandleError:
Call StandardError(Err.Number, Err.Description, ModName)
Resume ExitHere

4. I have a few pop-up modal forms that can only be closed by clicking a button on the form which runs a procedure with a single line of code like: DoCmd.Close acForm, "form name", acSaveNo. Should my error handling code be added to a simple procedure like this? If so, if there is somehow an error I think my code would display the error message and then exit, and the form would remain open, possibly trapping the user on that form with no way to close it?

Any general advice on my questions would be very helpful.

Thanks!

[June7]

Review http://allenbrowne.com/ser-23a.html

1. I think yes

3. Yes, could have a public procedure. I inherited an incomplete db with this but it was so annoying during development, I disabled it, never reactivated.

2 & 4 I don't have error handler in everything.

Note, error handlers are for end users' comfort, not developers. Error handlers can actually interfere with debugging so don't activate until ready to deploy project.

[Aaron5714]

Thanks for the advice; that is helpful.

[Aaron5714]

I have been doing some testing with this and noticed that the error number that my error handling message returns does not appear to be correct. See the screenshot below; I think this particular error should be 3022 (according to this site), but my code shows it as -2147217887. I'm using Err.Number to return the error number - is that not correct?

[June7]

Err.Number returns -2147217887? I agree, doesn't make sense but since I've not had to deal with it, never gave it any thought.

Why would this error be generated anyway? This looks like the result of modifying a table. Modifying structure should not be a process in a deployed database.

[Aaron5714]

I have a popup form that allows users to search for an account and then add that account to the contract they are viewing. When they select an account number the code below runs which adds that account to a table called "tblContractAccounts". The error in question is triggered when the user selects an account that has already been added for a given contract, thus violating the table's unique primary key. I had added some error handling to try and trap this error and provide a more understandable error message to the user. I was expecting this scenario to trigger error number 3022, rather than the strange number I am seeing.

Private Sub AccountID_Click()
        
    'get user confirmation
    If MsgBox("Do you want to add the account " & Me.AccountName & " to this contract?", vbQuestion + vbOKCancel, "Add New Account") = vbCancel Then
        Exit Sub
    End If
    
    'save the contract record if it is dirty
    If Forms!frmContractDetail.Dirty Then Forms!frmContractDetail.Dirty = False
    
    'set up and open the recordset
    Dim ContractAccountsRS As New ADODB.Recordset
    ContractAccountsRS.Open "tblContractAccounts", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    'add account to the contract accounts table
    With ContractAccountsRS
        .AddNew
        ![ContractID] = Forms!frmContractDetail!ContractID
        ![AccountID] = Me.AccountID
        .Update
    End With
    
    'clean up
    ContractAccountsRS.Close
    Set ContractAccountsRS = Nothing
    
    'close search form and requery contract detail form
    DoCmd.Close acForm, "frmSearchAccount", acSaveNo
    Forms!frmContractDetail!frmContractAccountsDS.Requery
            
End Sub

[June7]

Don't allow the error to happen. Check the table for the record before allowing append. If already exists, tell user to pick another.

I use DLookup a lot for this.

[Aaron5714]

Thanks! Yeah I figured that was a better way to handle this.