469,323 Members | 1,560 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Error Handling while Inserting data

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
4 8740
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
3,080 Expert 2GB
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
8,800 Expert 8TB
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
32,173 Expert Mod 16PB
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.

Similar topics

1 post views Thread by Wayno | last post: by
1 post views Thread by Sean Abrahams | last post: by
13 posts views Thread by Thelma Lubkin | last post: by
1 post views Thread by michaeltorus | last post: by
35 posts views Thread by jeffc226 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.