Find Duplicates in two fields against Another two fields

  • Thread starter Thread starter PR
  • Start date Start date
P

PR

I am trying to find out when a user enters an account number and account
name into a from I want to be able to check that these detail does not
exists and the old account number or old account name, if it does I what to
flag a record to indicate this is the case.

Does anyone have a way of achieving this?

Regards PR
 
If you want to guarantee there is no duplicate, open your table in design
view, select the Account field, and and in the lower pane of table design
set the Indexed property to "Yes (No Duplicates)"

If you want to give a warning but let the user override it, use some code in
the BeforeUpdate event procedure of the form where entries are made. This
kind of thing:

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

If Me.[Account] = Me.[Account].OldValue Then
'do nothing
Else
strWhere = "[Account] = """ & Me.[Account] & """"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
bWarn = True
strMsg = strMsg & "Account number exists." & vbCrLf
End If
End If

'do the same thing for the account name.

If bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Possible duplicate") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End Sub
 
Back
Top