Solved

Trap error in case of Key violation append query

Posted on 2004-03-26
5
659 Views
Last Modified: 2009-07-29
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
Comment
Question by:roelfrissen2
  • 2
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10686669
to find out what the exact error number is, why dont you display the err.number
so then you what your checking for
0
 
LVL 4

Expert Comment

by:matt150279
ID: 10686730
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
 
LVL 4

Accepted Solution

by:
matt150279 earned 125 total points
ID: 10686758
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
 

Author Comment

by:roelfrissen2
ID: 10686804
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
 

Author Comment

by:roelfrissen2
ID: 10688117
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now