• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

Trap error in case of Key violation append query

In my form where I run an append query with a command button I want to trap the error message in case of an key violation.
I read in several answers on expert exchange that the err.number for keyviolation  is 3022....

The next vb code is situated at the onclick event of my button:

On Error GoTo err_handle

DoCmd.OpenQuery "qryArtikelToevoegen"

err_handle:
If Err.Number = 3022 Then
 MsgBox "Precies hetzelfde artikel is al ingevoerd bij deze order, verhoog de aantallen handmatig."
Else
MsgBox Err.Description & vbNewLine & Err.Number
End If

Unfortunally I stil get the standard error message for a key violation....
In the debugging mode the error handel directly jumps to the "else" statement, so apparently, the err.number is not 3022!!??
What is wrong with this code???

Thanx Roel Frissen
0
roelfrissen2
Asked:
roelfrissen2
  • 2
  • 2
1 Solution
 
rockiroadsCommented:
to find out what the exact error number is, why dont you display the err.number
so then you what your checking for
0
 
matt150279Commented:
If, in the error trapping section you type

msgbox err.number

Then whenever the error comes up you will see what number it is, and then you can trap the error properly with the correct number.
0
 
matt150279Commented:
On second thoughts, it looks like you will get that error no matter what error trapping you put in.  This is because you are directly calling the saved query "qryArtikelToevoegen".

To get it to do what you want, I would suggest not directly calling the query, but to create a querydef at runtime and execute it against the database then.  If you do it this way you will have more control and you will be able to trap the error.
0
 
roelfrissen2Author Commented:
matt150279, you're right.. I don't get the error, indeed the problem is the query.. next point: how do i create a query def at runtime...

My append query is rather complex.. :

INSERT INTO tblArtikel ( artikelNr, kleur, maatID, naam, merk, aantalInBestelling, inkoopprijs, inkoopDatum, orderNr )
SELECT Forms!frmmain!frmBestelling.form!artikelNr AS Expr1, Forms!frmmain!frmBestelling.form!kleur AS Expr2, tblMaat.maatID, Forms!frmmain!frmBestelling.form!naam AS Expr3, Forms!frmmain!frmBestelling.form!merk AS Expr4, tblMaat.aantal, Forms!frmmain!frmBestelling.form!inkoopprijs AS Expr5, Forms!frmmain!frmBestelling.form!bestelDatum AS Expr6, Forms!frmmain!frmBestelling.form!orderNr AS Expr7
FROM tblMaat
WHERE (((tblMaat.aantal)>0));
0
 
roelfrissen2Author Commented:
this works :-)

on error goto err_handle

 Dim db As DAO.Database
 Dim strSQL As String
   
 Set db = CurrentDb
   
 strSQL = "INSERT INTO tblArtikel ( artikelNr, kleur, maatID, naam, merk, aantalInBestelling, inkoopprijs, orderNr) Select '" & Forms!frmmain!frmBestelling.Form!artikelNr & "' , '" & Forms!frmmain!frmBestelling.Form!kleur & "' , maatid , '" & Forms!frmmain!frmBestelling.Form!naam & "' , " & Forms!frmmain!frmBestelling.Form!merk & " , aantal , '" & Forms!frmmain!frmBestelling.Form!inkoopprijs & "' , '" & Forms!frmmain!frmBestelling.Form!orderNr & "' FROM tblMaat where tblmaat.aantal >0 ;"

db.Execute strSQL, dbFailOnError


Forms!frmmain!frmBestelling!tblArtikelsubform.Form.Requery

err_handle:
If Err = 3022 Then
 Err.Clear
MsgBox "Precies hetzelfde artikel is al ingevoerd bij deze order, het aantal artikelen in vooraad van dit artikel wordt opgehoogd."
DoCmd.OpenQuery "qryupdateaantallen"
Forms!frmmain!frmBestelling!tblArtikelsubform.Form.Requery
Else
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now