Error Handling while Inserting data

[anisu]

Hi,
I am trying to do error handling during insert in MS Access 2002 (OS: MS XP)

The problem is that when a duplicate record is added for the primary key field or a null value included in a unique filed, an error appears:

"Microsoft Access cannot append all the records in the append query!"

What I want is to suppress this message and display my own message, but both this message and mine one comes while I do the error handling.

Please suggest. I have included the code. Using "Docmd.Setwarnings false" suppresses the display of both the above message as well as my message -- it actually doesn't do the error handling.

Also, where can I find the list of errors in Access and their error numbers?

Private Sub cmdsave_Click()
 
On Error GoTo errmsg    
    Form.Requery
 
    DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
    & "Citizenship, CitizenNo, FirmName ) " _
    & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
    & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
    & "FROM tmpmembers"
 
exit_cmdsave:
    Exit Sub
 
errmsg:
 
    If Err = 3022 Then     
        
                 <-- this is not working
 
 
        MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
 
    ElseIf Err = 3058 Then
 
        MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
 
    Else
        MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
 
    End If
    Resume exit_cmdsave
 
End Sub
 

[abouddan]

Hi,
I am trying to do error handling during insert in MS Access 2002 (OS: MS XP)

The problem is that when a duplicate record is added for the primary key field or a null value included in a unique filed, an error appears:

"Microsoft Access cannot append all the records in the append query!"

What I want is to suppress this message and display my own message, but both this message and mine one comes while I do the error handling.

Please suggest. I have included the code. Using "Docmd.Setwarnings false" suppresses the display of both the above message as well as my message -- it actually doesn't do the error handling.

Also, where can I find the list of errors in Access and their error numbers?

Private Sub cmdsave_Click()
 
On Error GoTo errmsg    
    Form.Requery
 
    DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
    & "Citizenship, CitizenNo, FirmName ) " _
    & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
    & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
    & "FROM tmpmembers"
 
exit_cmdsave:
    Exit Sub
 
errmsg:
 
    If Err = 3022 Then     
 
        MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
 
    ElseIf Err = 3058 Then
 
        MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
 
    Else
        MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
 
    End If
    Resume exit_cmdsave
 
End Sub
 

I think the error number for the duplicate IDNO is not 3022 it's 2501.
I tested it on my machine and it worked very well.
Note that when you are promted that an error occured and you have to choose between yes no and Help , you have to choose No.

[nico5038]

Error messages may differ depending on the installed Access/JetEngine version.
Best to do a check for an existing ID before issuing the INSERT.
You could use a DLOOKUP like:

IF IsNull(DLOOKUP("IDNo","members","IDNo=" & Me.IDNo)) then

This assumes that the IDNo is a field on your form.

Nic;o)

[ADezii]

Hi,
I am trying to do error handling during insert in MS Access 2002 (OS: MS XP)

The problem is that when a duplicate record is added for the primary key field or a null value included in a unique filed, an error appears:

"Microsoft Access cannot append all the records in the append query!"

What I want is to suppress this message and display my own message, but both this message and mine one comes while I do the error handling.

Please suggest. I have included the code. Using "Docmd.Setwarnings false" suppresses the display of both the above message as well as my message -- it actually doesn't do the error handling.

Also, where can I find the list of errors in Access and their error numbers?

Private Sub cmdsave_Click()
 
On Error GoTo errmsg    
    Form.Requery
 
    DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
    & "Citizenship, CitizenNo, FirmName ) " _
    & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
    & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
    & "FROM tmpmembers"
 
exit_cmdsave:
    Exit Sub
 
errmsg:
 
    If Err = 3022 Then     
 
        MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
 
    ElseIf Err = 3058 Then
 
        MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
 
    Else
        MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
 
    End If
    Resume exit_cmdsave
 
End Sub
 

There have been several Posts relating to this very Topic. To suppress the standard Access Error Message and display your own, custom Error Message, you need to write code in the Form's Error() Event, trap the specific Error (which Err.Number will return), and set the acDataErrContinue CONSTANT:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = XXXX Then      'Trap specific Error here
  MsgBox "Your specific Error Message", vbExclamation, "Blah, Blah, Blah"
  Response = acDataErrContionue  'do not display the Default Error Message
End If
End Sub

[NeoPa]

That's good stuff ADezii, but I think a better solution is to check before adding the record (As per Nico's post #2).
Error handling is good, but relying on it in the logic of the code is not generally to be recommended IMHO. If for no other reason than the error handling settings on an individual PC may differ from those expected. That and simplicity of code.