Double Booking Problem

[ewan89]

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 a have seen but am getting an error
for "Cancel = True". The error is "Compile error: variable not
defined".

Code:

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

[raskew via AccessMonster.com]

Hi -

Take a look at Allen Browne's Clashing Events application at
http://allenbrowne.com/tips.html. Think it may well be what you're after.

HTH - Bob

ew****@gmail.com wrote:

>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 a have seen but am getting an error
for "Cancel = True". The error is "Compile error: variable not
defined".

Code:

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[Tom Clavel]

Cancel is defined in the beforeUpdate event, not onCurrent.
Try your code there. -tc

I have tried to modify the code a have seen but am getting an error
for "Cancel = True". The error is "Compile error: variable not
defined".