Searching For Duplicate Records

Hi all,

With some help from this forum (esp. Pat Hartman), i've been able to code up a script that performs a check on duplicate values.

My database has a Room Bookings form which consists of the room name, periods and booking date (BDate) fields. I need to prevent a duplicate on the same date, period and room.

The only problem with my code is that it only does a check on one of the fields e.g. BDate although i select a different period or room it still comes up with an error message saying that booking already exists? This means it is only checking the duplicate value of 1 field and not a combination of fields.

Here is my code:



Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSearch As String
Dim varKey As Variant

strSearch = "BDate = #" & Me.BDate & "# And Period1 = """ & Me.Period1 & """And Period2 = """ & Me.Period2 & """And Period3 = """ & Me.Period3 & """And Period4 = """ & Me.Period4 & """And Period5 = """ & Me.Period5 & """And Period6 = """ & Me.Period6 & """And Lunch = """ & Me.Lunch & """And After_School = """ & Me.After_School & """"
varKey = DLookup("Booking_ID", "Furtherbookings", strSearch)
If Not IsNull(varKey) Then
If MsgBox("Booking already exists Booking ID: " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
Else
Cancel = True
Me.Undo
End If
End If


End Sub


Can any1 identify where i am going wrong?

I'd really appreciate any help/guidance to resolve this problem!

:(