Macros and Programming


CardboxForumsMacros and Programming > "Access On Error GoTo"

Access On Error GoTo

Problem on Access On Error GoTo

Posted By Post


21-Jul-2006 16:52

Hi All,

This is an excerpt of my code. I am trying to handle an error before inserting data into mytable. The expected error is "duplicate primary key". However, the system bypass to trap the error (the statement On Error GoTo PROC_ERR3 is not executed). I have also this statement (DoCmd.SetWarnings (False)) somewhere on top of my procedure. When I rem out this statement, I get the exepected error message, which is relationship violation. Anyone, who has clue, please recue me.

Thank you

If [field1] = strRS.Fields.Item("field2") Then
strError = "This item is already assigned to another user"
On Error GoTo PROC_ERR3
strSQL = "INSERT INTO mytable (userID, pcsn) VALUES ([userID],'" & struserregno & "')"
DoCmd.RunSQL strSQL
MsgBox "error didn't happen"


Paul Irvine

24-Jul-2006 08:54

I'm not sure if On Error Goto linenumber is supported by VBScript, so you may want to try On Error Resume Next and check if Err.number <> 0. You can switch error handling off using On Error Goto 0.

Have a look at http://www.webgecko.com/products/apgen/docs.asp?page=errlanguageerrorhandling.htm for some examples.


25-Jul-2006 11:04

Hi Irvine,
thank you for replying!
The Resume Next command has the same effect as the on Error Go to command.
The problem is that whether there is an error or not the value of Err.number stays 0 so not possible to execute required codes based on the Err.number.

with DoCmd.SetWarnings () Set to "True" and on Error Go To satement in place, I get the ususal Ms.Access error messages:
1. You are about to append one row yes/No ?
2.Ms.Access can't append, Do you want to run the action qery any way Yes/No?

My reply would be "yes" to question 1 and "No" to question 2

then only I get the correct code executed in my program i.e code jumps to line given on Error Go To statement. And when No error it does insert the record in the mytable, the no 1. message above does pop-up and I need to choose yes.

Any other way to approach this problem?

Paul Irvine

25-Jul-2006 11:26

If the On Error mechanism isn't working, perhaps you could do a SQL SELECT to find out if a record exists in Access with the same unique key.

© 2010 Cardbox Software Limited   Home