Solved

Trap error in case of Key violation append query

Posted on 2004-03-26
5
660 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

785 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