checking for duplicate records 1

[mgbeye]

I would like to have my form check for duplicate records before saving. If there is an exact duplicate I would like to have it bring up an message that tells them and then gives them a choice of going ahead to add the record or not. Something like "This record already exists. Would you like to add a duplicate?" then have a yes button and a no button. My problem is this. I have a number of fields to check. Is there an easy way to do this?
These are the fields I need to check:

sap number
date
line
shift
pallet tag count
palletizer count

I only want to display a message if ALL fields are duplicates.

Can anyone help? thanks!

 

[gny]

My solution would be to open a recordset in code, with the entered data as criteria for each field. If the recordset is not empty, there is a duplicate in the database.
Instead of building the query in code as below you could also use a predefined parameter query, which should be faster.

Something like this:

Dim dbDatabase As Database
Dim sSql As String
Dim rst As Recordset

sSql = &quot;SELECT * FROM <tablename> WHERE sap=&quot; & <sap_ctrl> & &quot; AND line=&quot; & <line_ctrl> & &quot;;&quot; '*** And so on....

Set dbDatabase = CurrentDb()
Set rst = dbDatabase.OpenRecordset(sSql)
If rst.RecordCount > 0 Then
    Msgbox (&quot;Duplicate&quot;)
    '*** Let user decide
Else
    '*** No duplicate 
End If

I hope I´ve been helpful

/gny