Message on "AfterUpdate" event:

[gold007eye]

I have a form with a field called "CCN" and what I need to do is have some sort of code that will look at the table "Application Data - NE" and check to see if there are any duplicate records for that CCN on the "AfterUpdate" function and if so; spit out an error message saying "CCN already exist; please try another".

Does anyone have any ideas on how to code this?

I know how to do the custom error message; I am just not sure how to have it check for duplicates before the rest of the form can be updated/before the record is saved.

 

[MStef]

Try to do it with; DLookUp function, or DCount function.

 

[gold007eye]

Any idea on how to use DLookup function to find the duplicate? I know how to use it to fill in another field based on what it finds in the table.

 

[gold007eye]

Won't set focus back to CCN-LU field

Ok so I got this working using the DLookup function, but now my problem is that it won't set the focus back to the "CCN-LU" field when it detects a duplicate. Did I code this wrong?

Private Sub CCN_LU_AfterUpdate()
On Error GoTo Err_CCN_LU_AfterUpdate

Dim x As Variant
x = DLookup("[CCN]", "[Application Data - NE]", "[CCN]=[CCN-LU]")
Me![CCN] = x
Me![CCN].Requery

If IsNull([CCN]) Then
'Do Nothing. Continue forward.
'MsgBox "No Duplicate Value Exists; you may continue...", vbOKOnly, "No Duplicate CCN"
ElseIf Me![CCN].Value = Me![CCN-LU] Then
MsgBox "A Duplicate CCN already exists!" & vbCrLf & vbCrLf & "Please use a different CCN...", vbCritical, "Duplicate CCN Already Exists!"
Me![CCN-LU].Value = ""
Me![CCN-LU].SetFocus
End If

Exit_CCN_LU_AfterUpdate:
    Exit Sub

Err_CCN_LU_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_CCN_LU_AfterUpdate
End Sub