validation rules

[jacc14]

I am wanting to prevent duplicates if 2 fields already exist. I therefore set primary keys on both the fields. This works but it is only after completing a line that it tells me that i have duplicate records. I have tried to set up a recordset and it works to prevent a duplicate if one of the fields is already in there but i cant seem to compare 2 fields. This is my code but i want to add the second field.

 Dim PID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
PID = Me.JobNo.Value
stLinkCriteria = "[jobno]=" & "'" & PID & "'"
'Check StudentDetails table for duplicate StudentNumber
If DCount("jobno", "tbl_jobheader", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Job Number " _
& PID & " already exists in database.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
 
End If
Set rsc = Nothing
 

am i over complicating things

Many thanks

Christine.

[ChipR]

You've got the right idea with DCount. You can get rid of the rsc stuff, and change your criteria string to include both fields.

stLinkCriteria = "[jobno] = """ & PID _
 & """ AND [otherField] = """ & OtherStringValue & """"

[jacc14]

@ChipR
Many thanks for this. Problem solved.