Solved

Trap error in case of Key violation append query

Posted on 2004-03-26
5
664 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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