If input is duplicate

[SharonInGa]

Novice level, please....

I want a custom message to notify the user that his input is a duplicate of
an existing field (CompositeID). The user then needs the ability to either
abort or retype his input. This is what I have so far... which gives the
custom message but then flops.
-----------------------------------------------------------------------
Private Sub CompositeID_AfterUpdate()
Dim DupCompID As Integer

If CompositeID > 1 Then
DoCmd.SetWarnings False

DupCompID = MsgBox("Composite ID already exist", vbAbortRetryIgnore,
"errCompID")
Me.CompositeID.SetFocus

End If
End Sub

 

[tina]

Private Sub CompositeID_AfterUpdate()

suggest you move the code to the control's BeforeUpdate event, which can be
cancelled.

Dim DupCompID As Integer

you don't need this variable. see below.

If CompositeID > 1 Then
DoCmd.SetWarnings False

first, you don't need to turn off warnings just to show a message box and
cancel the update event. second, in VBA you must turn warnings back on
(DoCmd.SetWarnings True), otherwise the setting stays off until you close
the database.

DupCompID = MsgBox("Composite ID already exist", vbAbortRetryIgnore,
"errCompID")

you don't need to save the return value of the message box into a variable,
because you're not doing anything with the variable afterward. so you don't
need the variable, either. just run the message box by itself.

Me.CompositeID.SetFocus

by moving the code to the BeforeUpdate event procedure, you can cancel the
update when the If statement's equation returns True, so the focus will stay
in the control. instead of the above line of code, use

Cancel = True

End If
End Sub

hth