Macros and Programming

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"

PROC_ERR3:

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.

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?

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.