By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Double Booking Problem

P: n/a
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Me.NewRecord = True Then
  3. Dim strWhere As String, strMessage As String
  4. Dim rsClone As Recordset
  5.  
  6. strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.
  7. [BookingsSubform].Form![CourtID] & _
  8. ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.
  9. [BookingsSubform].[Form]![ScheduleID] & _
  10. ") AND ([BookingsTimeSubform].[Form]!
  11. [ScheduleEndTime]>=#" & _
  12. Format(Me.[BookingsTimeSubform].[Form]!
  13. [ScheduleStartTime], "Medium Time") & _
  14. "#) AND ([BookingsTimeSubform].[Form]!
  15. [ScheduleStartTime]<=#" & _
  16. Format(Me.[BookingsTimeSubform].[Form]!
  17. [ScheduleEndTime], "Medium Time") & _
  18. "#))"
  19.  
  20. Set rsClone = Me.RecordsetClone
  21. rsClone.MoveFirst
  22. rsClone.FindFirst strWhere
  23.  
  24. If rsClone.NoMatch Then
  25. MsgBox ("test")
  26. Cancel = True
  27. Exit Sub
  28. End If
  29. End If
  30. End Sub
  31.  
Any help would be very nice indeed.
John

Feb 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Me.NewRecord = True Then
  3.    Dim strWhere As String, strMessage As String
  4.    Dim rsClone As Recordset
  5.    strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.
  6. [BookingsSubform].Form![CourtID] & _
  7.               ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.
  8. [BookingsSubform].[Form]![ScheduleID] & _
  9.               ") AND ([BookingsTimeSubform].[Form]!
  10. [ScheduleEndTime]>=#" & _
  11.               Format(Me.[BookingsTimeSubform].[Form]!
  12. [ScheduleStartTime], "Medium Time") & _
  13.               "#) AND ([BookingsTimeSubform].[Form]!
  14. [ScheduleStartTime]<=#" & _
  15.               Format(Me.[BookingsTimeSubform].[Form]!
  16. [ScheduleEndTime], "Medium Time") & _
  17.               "#))"
  18.    Set rsClone = Me.RecordsetClone
  19.    rsClone.MoveFirst
  20.    rsClone.FindFirst strWhere
  21.    If rsClone.NoMatch Then
  22.        MsgBox ("test")
  23.       Cancel = True
  24.        Exit Sub
  25.    End If
  26. End If
  27. End Sub

Any help would be very nice indeed.
John
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

Feb 19 '07 #2

P: n/a
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".
Feb 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.