Trapping an Error (1 Viewer)

[Amileaux]

I have a table with has three fields as primary keys - to prevent duplicate information being input.

I have a form that uses an append query to add data to the table.
If the user tries to input a different dollar amount to an already existing Program/State/Date (the three primary keys) it will not append due to "key violations"

Okay so far.

Since I don't want the user to have to click okay to all the "we are about to append etc." messages that occur I have the following in my code:
docmd.setwarnings False (then reset to True at the end)
msgbox "Record Added"

Since the user doesn't see the message stating that the records were not appended and only sees the "record added", they are not aware that there is a problem.

So I took out the docmd.setwarnings False and included an "on error" event for the form that "intercepts" these messages -

So I now have error #8057 isolated and my message appears that they have attempted to make a duplicate entry.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 8057
MsgBox "You already have dollars associated with this"
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

However, now the user has to still click off on the messages that they are about to append...... AND my message box still lets tells them "Record Added". I've got something out of sync here. Any thoughts? My main code follows:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

If IsNull(Me![txtPlan].Value) Then Me![txtPlan].Value = 0
If IsNull(Me![txtCE].Value) Then Me![txtPlan].Value = 0

If IsNull(Me![txtPlanDate].Value) Or Me![txtPlanDate].Value = "" Then
MsgBox "Please Select a Month", vbOKOnly
[lstMnth].SetFocus
Exit Sub
End If

If Me![txtPlan].Value = 0 And Me![txtCE].Value = 0 Then
MsgBox "Please input a CE or Plan amount", vbOKOnly
Exit Sub
End If

'DoCmd.SetWarnings False
If Me![txtPlan] <> 0 And Not IsNull(Me![txtPlan]) Then
DoCmd.OpenQuery "qryAppendtblTarget", acViewNormal, acEdit
Forms![frmInputTargets]![subfrmtblTarget].Requery
MsgBox "Record Added To Plan", vbOKOnly
End If
If Me![txtCE] <> 0 Then
DoCmd.OpenQuery "qryAppendtblCE", acViewNormal, acEdit
Forms![frmInputTargets]![subfrmtblCE].Requery
MsgBox "Record Added To CE", vbOKOnly
End If
'DoCmd.SetWarnings True

Exit_cmdAddRecord_Click:
Exit Sub
Err_cmdAddRecord_Click:
MsgBox "Error number: " & Err.Number & " - " & Err.Description
Resume Exit_cmdAddRecord_Click
End Sub

 

[GumbyD]

Amileaux -

Because your warnings are stilled turned on Access is just processing your error message on top of it's normal messages. I think since you know what is causing the issue and since you are only added one record at a time from this form, I would just add a SQL statement to check and see if there is a record out there with a matching key combination and then put up a message box to let the user know that a record already exists - then just exit the function. If there is no key match (your SQL returns a "no match" or EOF then you just turn off the messages and continue with the append query.

I hope that makes sense - if not let me know the name of the table you are appending to and I can alter your code to make the check.

GumbyD