Checking for duplicates

[MelissaBerrett]

I am trying to search a table after the user enters a record for a duplicate record. My two fields that can not be the same are building number and check number. (There can be duplicate check numbers and building numbers, but only one check number for one building number) I have this code on the on_click event on the ADD command button. It's not working. Do I have the code in the right place? What seems to be going wrong? Thanks for the help!


Dim db As database
Dim rec As Recordset
Set db = opndatabase(Check_Register_FrontEnd)
Set rec = Check_Register_FrontEnd(tblCheckReg)
rec.findfirst ("field3=x and field4=y&quot
If rec.nomatch Then
rec.AddNew
rec!field3 = x
rec!field4 = y
rec.Update
Else
MsgBox ("Record Already Exists&quot
End If
rec.Close
Set rec = Nothing
db.Close
Set db = Nothing

 

[MelissaBerrett]

Now I'm trying to take the SQL approach I found in a keyword search. I'm getting a missng operator error, and I overall don't know if the code is working. Can anyone help? Thanks!

Dim rec As Recordset
Set rec = CurrentDb.OpenRecordset("SELECT * FROM tblCheckReg WHERE (([tblCheckReg].[Facility_Number])= '" & Me.Facility_Number.Value & &quot AND (([tblCheckReg].[Check_Number])= '" & Me.Check_Number.Value & "&quot
If rec.RecordCount > 0 Then
MsgBox "Record Already Added"
Else
rec.AddNew
rec.Update
rec.Close
End If
Set rec = Nothing

 

[xlbo]

Personally, I would take a slightly different approach - Concatenate building number and check number into a new field in your table. Make this field a primary key
Voila - it will not allow duplicates to be entered so you don't need to check for them Rgds
~Geoff~

 

[MelissaBerrett]

Do I do this using a query, and when I import my data into my table, with this effect it? thank you for your help!