Need help w/ Error function

[Mike]

I have a tables that uses 2 indexed fields. If the user would create a
duplicate
entry then the customized message box below (***s) appears and the selected
value cannot be entered.

However, as of now, I either have to select a new value (from a list) or
backspace
the entered value and then delete the blank row. Either way this is not
what I want.

Does anyone know how to customize the function below so that it
automatically deletes the duplicate value that was attempted to be added to
the record set (after having clicked OK on the message box)?

In essense, the form is still open and it appears as is the error never
happened. The user can add a new record (and may get the error again if
the wrong record is selected again).




**************
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
MsgBox "This value already exists for the selected entity - please
enter unique value!"
Response = acDataErrContinue

End If
End Sub
**************


Thanks,
Mike

 

[Ken Snell]

Add this line to the code:

Me.Undo

 

[Mike]

Ken:

thanks! I just tried it out and it does remove the invalid data entry, but
it also leaves a blank record (unless the user reselects another value).
To be on the safe side, is possible to also delete the blank line
automatically?

Thanks,
Mike

 

[Mike]

Ken:

I have come up w/ a solution... it may not be the smoothest way but it does
work.

Me.Undo is now followed by the line:
CurrentDb.Execute "DELETE tblBranch.Branch FROM tblBranch WHERE
(((tblBranch.Branch) Is Null))"


Mike

 

[Van T. Dinh]

I would suggest a smoother method is to validate data
(including checking for duplicates) in the
Form_BeforeUpdate Event. If data is invalid, you can give
the user a message and cancel the Update Event.

HTH
Van T. Dinh
MVP (Access)