Searching for Duplicate Records

[Access_Help]

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!

 

[Access_Help]

Anyone out there that can help me?