Duplicate records

[Carl]

dear all
I need to create an after event code that will check
previous records based on forgein keys "EMEI ID"
and "Status ID".
If there are duplicates of "EMEI ID" in the table then all
the previous records must be a "Status ID" of 9
(superseded).

If there is a duplicate and the one of the previous
records is not '9' then I need a message box and the field
to revert to the pre update data.

I've got this so far:

Dim strDBconn As Database

Set strDBconn = CurrentDb
Dim strSQL
Dim SupIssue

strSQL = "SELECT [EMEI ID], [Status ID] FROM [EMEI
Detail] WHERE [EMEI ID] = " & Me.emei_id & " AND [Status
ID] <> 9"

Set SupIssue = strDBconn.OpenRecordset(strSQL)

If SupIssue.RecordCount = 0 Then
MsgBox "Supersede the previous issue."
Else
' All OK
End If

 

[Allen Browne]

DLookup() returns Null if there is no match.
Look for a duplicate with the same number and Status not 9.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If (Me.[EMEI ID].Value = Me.[EMEI ID].OldValue) _
OR (Me.[Status ID] = 9) Then
'do nothing
Else
strWhere = "([EMEI ID] = """ & Me.[EMEI ID] & _
""") AND ([Status ID] <> 9)"
varResult = DLookup("EMEI ID", "EMEI Detail", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Duplicate of " & varResult
End If
End If
End Sub