Solved

Trap error in case of Key violation append query

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

830 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