Double Booking Problem

[johnblack]

I am creating a tennis court booking system. I am trying to create a macro that will not allow the same court to be booked on the same date at the same time.

I have the following tables and fields

tblMembers
MemberID
FirstName
LastName
etc

tblCourts
CourtID
CourtName

tblSchedule
ScheduleID
ScheduleDate
CourtID

tblSheduleDetails
SheduleDetailsID
SheduleID
MemberID
SheduleStartTime
SheduleEndTime

I have tried to modify the code form a similar post but am getting an error for "Cancel = True"

Private Sub Form_Current()
If Me.NewRecord = True Then
    Dim strWhere As String, strMessage As String
    Dim rsClone As Recordset
 
    strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
               ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
               ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
               "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
               Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
               "#))"
 
    Set rsClone = Me.RecordsetClone
    rsClone.MoveFirst
    rsClone.FindFirst strWhere
 
    If rsClone.NoMatch Then
        MsgBox ("test")
       Cancel = True
        Exit Sub
    End If
End If
End Sub

Any help would be very nice indeed.
John

[NeoPa]

New post moved to it's own thread (Access 2007 Template Appointment Manager)

PS. Never mind. Not only a hijack but also a double-post. Presumably a new user of forums. Anyway, the remaining thread is found at tennis court booking database.