Error Handling & Append Queries

[DarkWolff]

I'm trying to get an append query to throw an error when it encounters a key violation, but Access doesn't seem to be obliging.



I have a form that allows a user to add items from one subform to another, which is done with an append query. I've disabled warnings for the operation (by request), so when a duplicate entry is run I want add a prompt. However the built-in error handling from Access doesn't seem to consider this an error as no errors are being thrown at all when this happens.

Is this possible?

[June7]

Since errors are turned off, I suspect the record just won't be written even though the error message is not presented. Suggest you validate the user entry before running append.

[DarkWolff]

You are correct, the record won't be written (which is correct). However I want to be able to create a message box that informs the user why it wasn't written. I just can't seem to get it to trap the error (because Access isn't seeing it as an error). This is true regardless if I hide the warnings or not.

[orange]

A Form has an Error event.
Here is info from Access 2003 Help.
You could check your Acc2007 Help for Form Error Event for details.

Error Event

See AlsoApplies ToExampleSpecifics
The Error event occurs when a run-time error is produced in Microsoft Access when a form or report has the focus.
Private Sub object_Error(DataErr As Integer, Response As Integer)
Object The name of a Form or Report.
DataErr The error code returned by the Err object when an error occurs. You can use the DataErr argument with the Error function to map the number to the corresponding error message.
Response The setting determines whether or not an error message is displayed. The Response argument can be one of the following intrinsic constants.
Constant Description
acDataErrContinue Ignore the error and continue without displaying the default Microsoft Access error message. You can supply a custom error message in place of the default error message.
acDataErrDisplay (Default) Display the default Microsoft Access error message.
Remarks

This includes Microsoft Jet database engine errors, but not run-time errors in Visual Basic or errors from ADO.
To run a macro or event procedure when this event occurs, set the OnError property to the name of the macro or to [Event Procedure].
By running an event procedure or a macro when an Error event occurs, you can intercept a Microsoft Access error message and display a custom message that conveys a more specific meaning for your application.
Example

The following example shows how you can replace a default error message with a custom error message. When Microsoft Access returns an error message indicating it has found a duplicate key (error code 3022), this event procedure displays a message that gives more application-specific information to users.
To try the example, add the following event procedure to a form that is based on a table with a unique employee ID number as the key for each record.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
     Const conDuplicateKey = 3022
     Dim strMsg As String
      If DataErr = conDuplicateKey Then
         Response = acDataErrContinue
         strMsg = "Each employee record must have a unique " _
             & "employee ID number. Please recheck your data."
         MsgBox strMsg
     End If
End Sub

I'm not sure what happens if you turn Warnings Off.
Hope it's helpful.

[DarkWolff]

Thanks for the reply. The problem is the there is no error being thrown when the append query finds a duplicate record, regardless of whether or not the warnings are on or not. The query only adds appends one record based on the user's choice from another subform, so if the query fails it's fine if it just stops.

I tried using both the main form and the subform's error events, but nothing happens as Access is not considering this an error. I also read up on the Execute method, as that seems to have more reportable error controls but I can't see to get it to work with my saved query.

[pbaldy]

Just an untested shot in the dark, but you might try getting rid of the SetWarnings lines and use

CurrentDb.Execute "YourQueryHere", dbFailOnError

to run the query. The execute method doesn't throw the warnings, which is why you don't need the SetWarning lines, and it's possible that the dbFailOnError argument will tell you about the duplicates. Like I said, untested, so no warranty expressed or implied.

[DarkWolff]

Thanks for the post. When I try running the command with the Execute method (using a valid entry) I get:
Run-Time error '3061":
Too few parameters. Expected 4.

The code I used was: CurrentDb.Execute "APP_NY_TS_Add_Monthly", dbFailOnError

Using a DoCmd.OpenQuery causes the append query to work fine. The query pulls values a couple values from the subforms (textboxes), does that change anything?

[pbaldy]

Yes, the Execute method can't resolve the form references. One work around is to wrap them in the Eval() function:

Eval('Forms!FormName.ControlName')

[DarkWolff]

I now have everything running and am able to trap the error. Thanks so much for your help!

[pbaldy]

I'm curious, did the Execute method work out? I wasn't really sure if it would consider that an error, though I suspected it would.

[DarkWolff]

Yes, the Execute method worked when using the Eval workaround. If it tried to append with a duplicate key, Access threw error 3022, which is exactly what I wanted it to. Thanks so much!

[pbaldy]

Excellent! Happy to help.