Function that checks duplicate values

[Sreedhar]

I want to check whether the user enters a unique ID (such as CustomerID) each
time he enters new data.
I want to create a public function that accepts the Id to check and returns
true or false, I attempted one but it is not working. Here it is.
Can someone please help ?


Function CheckDuplicate(FieldToCheck As String, WhereToCheck As_
String,NewField As String) As Boolean
On Error GoTo Err_CheckDuplicate

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

CheckDuplicate = True

strSQL = SQLBuilderSelect(WhereToCheck, FieldToCheck)
strWhere = FieldToCheck & "=" & NewField

Set db = CurrentDb
Set rst = db.OpenRecordset(WhereToCheck, dbOpenDynaset)

With rst
Do While Not .EOF
.FindFirst strWhere
If .NoMatch Then
CheckDuplicate = True
Else
CheckDuplicate = False
End If
.MoveNext
Loop
End With

rst.Close
' Clear the objects
Set rst = Nothing
Set db = Nothing


Exit_CheckDuplicate:
Exit Function

Err_CheckDuplicate:
MsgBox err & " : " & Error
Resume Exit_CheckDuplicate

End Function

 

[Allen Browne]

Just use DLookup() to see if the value is already in the field:

Dim strWhere As String
Dim varResult As Variant
With Me.CustomerID
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
strWhere = .Name & " = " & .Value
varResult = DLookup(.Name, "tblCustomer", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate"
End If
End If
End With

Note that if CustomerID is a Text field (not a Number field), you need extra
quotes:
strWhere = .Name & " = """ & .Value & """"

 

[Sreedhar]

Allen,

I,ve been using the DLookup() function as below and it is working fine. But,
the problem is there are sevaral places in sevaral forms in the entire
application that I have to repeat the entire code for each control that needs
this validation. Hence, I want to create a public function that returns true
if match found, else false. this function is to be triggered at the Control's
beforeupdate event to do the validation.

Can it be done ? Any help is greatly appreciated.

My DLookup() code:

Private Sub CUSTOMER_ID_BeforeUpdate(Cancel As Integer)
On Error GoTo CUSTOMER_ID_Err

If (Eval("DLookUp(""[CUSTOMER ID]"",""[CUSTOMER - PERSONAL
PROFILE]"",""[CUSTOMER ID] = Forms![CUSTOMER - PERSONAL PROFILE]![CUSTOMER
ID] "") Is Not Null")) Then
Beep
MsgBox "The CUSTOMER ID you entered already exists. Enter a unique
NUMBER.", vbInformation, "Duplicate CUSTOMER ID !"
DoCmd.CancelEvent
End If


CUSTOMER_ID_Exit:
Exit Sub

CUSTOMER_ID_Err:
MsgBox err.DESCRIPTION
Resume CUSTOMER_ID_Exit

End Sub

 

[Allen Browne]

Okay, like you, I have code similar to this in most applications. I don't
have an elegant generic solution for you, so if someone else does we can
both benefit if they post a reply too.

The issue for me is that there are so many facets to this issue. The
delimiter to use in the 3rd DLookup() argument depends on the data type of
the field. The field might or might not be a required field. The unique
index that could be violated might or might not ignore nulls. The index
might be a multi-field index, so you might need to pass in an array of
fields, values, and data types. There is a timing issue here: for
single-field indexes, you might want to use BeforeUpdate of the control,
whereas for multi-field indexes you probably want to use BeforeUpdate of the
form. Then there's the OldValue to consider, i.e. if the user changes the
value back to the previous value, the value will be in the table, but is not
an index violation.

So, I have not succeeded in writting a generic function that easily accepts
all the possible arguments, builds the strings with the right delimiters,
identifies the potential unique indexes in the right table, handles the old
values, and returns a single yes/no which you can then interpret as a
violation or simply a warning.