MS Access VBA After update Prevent Duplicate Entry

[bbcdancer]

Working of a piece of VBA code to imform the user that his/her last
entry already existed in the table1 database.

Not sure why the VBA code does not work to identify the last text file
entry might a duplicate entry.

Any help I would be appreciated.

Brenda XXX

This is my code:

Private Sub TRADE_ID_AfterUpdate()

Dim Db1a As Database, rsCust1 As Recordset, strSQL As String

Set Db1a = CurrentDb

strSQL9 = "Select [EFC_CASE_REF] from table1 Where "
strSQL9 = strSQL & "[EFC_CASE_REF] = '" & Me.EFC_CASE_REF & "'"

Set rsCust1 = Db1a.OpenRecordset(strSQL9, DB_OPEN_DYNASET)
If rsCust1.EOF Then

MsgBox " Not a Duplicate."

Else

MsgBox " Yes a Duplicate."

End If

rsCust.Close
Db1a.Close

End Sub

 

[Guest]

It needs to be either in the Before Update or Before Insert events. By the
time you get to the After Update event, it is too late.
I have taken the liberty of rewriting your code to be more efficient.

Private Sub TRADE_ID_BeForeUpdate()

If Not IsNull(DLookup("[EFC_CASE_REF]", "table1", _
"[EFC_CASE_REF] = '" & Me.EFC_CASE_REF & "'")) Then
MsgBox "Duplicate Entry"
Cancel = True
End If

End Sub