By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,166 IT Pros & Developers. It's quick & easy.

Error Handling while Inserting data

P: 1
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsave_Click()
  2.  
  3. On Error GoTo errmsg    
  4.     Form.Requery
  5.  
  6.     DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
  7.     & "Citizenship, CitizenNo, FirmName ) " _
  8.     & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
  9.     & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
  10.     & "FROM tmpmembers"
  11.  
  12. exit_cmdsave:
  13.     Exit Sub
  14.  
  15. errmsg:
  16.  
  17.     If Err = 3022 Then     
  18.         
  19.                  <-- this is not working
  20.  
  21.         MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
  22.  
  23.     ElseIf Err = 3058 Then
  24.  
  25.         MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
  26.  
  27.     Else
  28.         MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
  29.  
  30.     End If
  31.     Resume exit_cmdsave
  32.  
  33. End Sub
  34.  
Feb 17 '07 #1
Share this Question
Share on Google+
4 Replies


abouddan
P: 42
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsave_Click()
  2.  
  3. On Error GoTo errmsg    
  4.     Form.Requery
  5.  
  6.     DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
  7.     & "Citizenship, CitizenNo, FirmName ) " _
  8.     & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
  9.     & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
  10.     & "FROM tmpmembers"
  11.  
  12. exit_cmdsave:
  13.     Exit Sub
  14.  
  15. errmsg:
  16.  
  17.     If Err = 3022 Then     
  18.  
  19.         MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
  20.  
  21.     ElseIf Err = 3058 Then
  22.  
  23.         MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
  24.  
  25.     Else
  26.         MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
  27.  
  28.     End If
  29.     Resume exit_cmdsave
  30.  
  31. End Sub
  32.  




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.
Feb 17 '07 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 17 '07 #3

ADezii
Expert 5K+
P: 8,638
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsave_Click()
  2.  
  3. On Error GoTo errmsg    
  4.     Form.Requery
  5.  
  6.     DoCmd.RunSQL "INSERT INTO members ( IDNo, Name, MemberType, Designation, Address, " _
  7.     & "Citizenship, CitizenNo, FirmName ) " _
  8.     & "SELECT tmpmembers.IDNo, tmpmembers.Name, tmpmembers.MemberType, tmpmembers.Designation, " _
  9.     & "tmpmembers.Address, tmpmembers.Citizenship, tmpmembers.CitizenNo, tmpmembers.FirmName " _
  10.     & "FROM tmpmembers"
  11.  
  12. exit_cmdsave:
  13.     Exit Sub
  14.  
  15. errmsg:
  16.  
  17.     If Err = 3022 Then     
  18.  
  19.         MsgBox "The IDNO already exists. Please type another one!",  vbOKOnly, "Duplicate ID Error"
  20.  
  21.     ElseIf Err = 3058 Then
  22.  
  23.         MsgBox "IDNO cannot contain Blank!", vbOKOnly, "Blank ID!"
  24.  
  25.     Else
  26.         MsgBox Err.Number & ":" & " " & Err.Description, , "Error!"
  27.  
  28.     End If
  29.     Resume exit_cmdsave
  30.  
  31. End Sub
  32.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  2. If DataErr = XXXX Then      'Trap specific Error here
  3.   MsgBox "Your specific Error Message", vbExclamation, "Blah, Blah, Blah"
  4.   Response = acDataErrContionue  'do not display the Default Error Message
  5. End If
  6. End Sub
Feb 17 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 18 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.