Duplication check on entering a record

[Jetjefferson]

Can anyone tell me if there is a way to check whether the info being entered to a form is already stored in the form. i.e. by checking the telephone number to see if there is another record with the same number.

What I want to happen in this circumstance is for a warning box to come up advising that these details are already held in the database.

Any assistance will be much appreciated.

Regards

Derek

 

[RuralGuy]

How about a DCount() or DLookup() in the BeforeUpdate event of the controls?

 

[FizzyFish]

You could also modify the code to run a query and check the resultset to see if anything returned in the BeforeUpdate event.

Here is an example of something that might work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSQL as string, rst as new adodb.recordset, cmd As New ADODB.Command

'initialize Recordset
Set rst = New ADODB.Recordset

strSQL = "SELECT * FROM table WHERE PhoneNumber like '*" & me.txtphone & "*'"

'Setup Command Object
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 60
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

'open recordset using command
rst.Open cmd, , adOpenStatic, adLockOptimistic

If rst.RecordCount > 0 then
'A record that is like the phone number has been found
If MsgBox("A record already exists with this phone number. Do you wish to cancel this entry?", vbYesNo + vbExclamation, "DUPLICATE ENTRY FOUND") = vbYes Then
Cancel = True
END IF
End if

End Sub