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

Double Booking Problem

P: 16
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"

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.[BookingsSubform].Form![CourtID] & _
  7.                ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
  8.                ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
  9.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
  10.                "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
  11.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
  12.                "#))"
  13.  
  14.     Set rsClone = Me.RecordsetClone
  15.     rsClone.MoveFirst
  16.     rsClone.FindFirst strWhere
  17.  
  18.     If rsClone.NoMatch Then
  19.         MsgBox ("test")
  20.        Cancel = True
  21.         Exit Sub
  22.     End If
  23. End If
  24. End Sub
Any help would be very nice indeed.
John
Feb 19 '07 #1
Share this Question
Share on Google+
50 Replies


P: 16
Just to let the know the error is "Compile error: variable not defined"
Feb 19 '07 #2

MSeda
Expert 100+
P: 159
try moving your code to the form's before update event. that is the correct event for your validation.
Feb 19 '07 #3

NeoPa
Expert Mod 15k+
P: 31,263
As MSeda says, the code is from a
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. End Sub
...procedure where the variable Cancel is predefined.
Trying to use this code in an OnCurrent event procedure will not work as there is no variable defined there for it to make sense. Hence your error message.
Feb 19 '07 #4

P: 16
Ok i have moved the code to the before update event (thanks Mseda) but double bookings are still allowed (the code has no effect). Any ideas from anyone regarding the code.
Thanks
John
Feb 19 '07 #5

Rabbit
Expert Mod 10K+
P: 12,324
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.[BookingsSubform].Form![CourtID] & _
  7.                ") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
  8.                ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
  9.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
  10.                "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
  11.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
  12.                "#))"
  13.  
  14.     Set rsClone = Me.RecordsetClone
  15.     rsClone.MoveFirst
  16.     rsClone.FindFirst strWhere
  17.  
  18.     If rsClone.NoMatch Then
  19.         MsgBox ("test")
  20.        Cancel = True
  21.         Exit Sub
  22.     End If
  23. End If
  24. End Sub
Your search criteria is set up wrong. You're using [BookingsSubform].Form![CourtID] when you should be using the name of the field from the table.
Feb 19 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
In other words as Rabbit says ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  3.  
Mary
Feb 20 '07 #7

P: 16
Sorry if i am a slow understandig what you guys mean but i am a newbie to VBA. Now i have this code:
Expand|Select|Wrap|Line Numbers
  1.     strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  2.                ") AND ([ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate] & _
  3.                ") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
  4.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
  5.                "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
  6.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
  7.                "#))"
  8.  
But it still doesnot work.
Any more ideas?
Thanks
John
Feb 20 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. AND [BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
  2.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
  3.                "#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
  4.                Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
  5.  
All this is doing is comparing values on the form. What is it you are trying to achieve here. If you explain the logic of the criteria I can help further.

Mary
Feb 20 '07 #9

P: 16
I am trying to not allow double bookings to be entered in the form. This involves not allowing a particular court to be booked on a particular date for a time that is already taken. For example
Court 1, 18/02/07, 11:00 AM - 01:00 PM and
Court 1, 18/02/07, 11:00 AM - 12:00 PM and
Court 1, 18/02/07, 11:00 AM - 01:00 PM should not be allowed as there are repeats and overlaps

But other entries such as
court 1, 18/02/07, 12:00 PM-01:00 PM and
court 1, 18/02/07, 01:00 PM-02:00 PM and
court 1, 18/02/07, 02:00 PM-05:00 PM should be allowed as there are no repeats or overlaps.

Hope that helps tell me if you need any more detail.
Thanks, John
Feb 20 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi John,

Your code won't work because the RecordsetClone won't allow you to reference the start and end times of the bookings. Try the following instead:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim db as DAO.Database
  3. Dim rs as DAO.Recordset
  4.     Dim strMessage As String
  5. Dim startTime As Date
  6. Dim endTime As Date
  7. Dim test As Boolean
  8.  
  9.   test = False
  10.   If Me.NewRecord = True Then
  11.     set db = CurrentDb
  12.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  13.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  14.        "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
  15.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  16.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
  17.  
  18.     startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
  19.     endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
  20.     If rs.RecordCount=0 Then Exit Sub
  21.     rs.MoveFirst
  22.     Do Until rs.EOF
  23.       If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
  24.         test = True
  25.       ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
  26.         test = True
  27.       ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
  28.         test = True
  29.       End If
  30.       If test Then 
  31.         rs.MoveLast
  32.       Else
  33.         rs.MoveNext
  34.       End If
  35.     Loop
  36.  
  37.     If test=False Then
  38.       MsgBox ("Time is available")
  39.     Else
  40.       MsgBox ("Time is unavailable")
  41.       Cancel = True
  42.     End If
  43.   End If
  44.  
  45.   rs.Close
  46.   Set rs=Nothing
  47.   Set db=Nothing
  48.  
  49. End Sub
  50.  
I really think there has to be a better way to do this though.

Mary
Feb 20 '07 #11

P: 16
Mary, thanks for your time on this.

Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  3.        "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
  6.  
This code was highlighted in red with the error "compile error: expected: list seperator or )". I couldnt solve this sorry to be a pain.
John
Feb 20 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry my fault the ending bracket should be outside the quote as follows:

Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  3.        "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
  6.  
Feb 20 '07 #13

P: 16
Im sorry to say the code has no effect. I dont know what else to try.
Any ideas?
Thanks for your time anyway.
John
Feb 20 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Try removing it from the form event altogether and put it in the after update event of the ScheduleEndTime control instead with some small amendments as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleEndTime_AfterUpdate()
  2. Dim db as DAO.Database
  3. Dim rs as DAO.Recordset
  4.     Dim strMessage As String
  5. Dim startTime As Date
  6. Dim endTime As Date
  7. Dim test As Boolean
  8.  
  9.   test = False
  10.   If Me.NewRecord = True Then
  11.     set db = CurrentDb
  12.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  13.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  14.        "ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule  ID " & _
  15.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  16.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
  17.  
  18.     startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
  19.     endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
  20.     If rs.RecordCount=0 Then Exit Sub
  21.     rs.MoveFirst
  22.     Do Until rs.EOF
  23.       If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
  24.         test = True
  25.       ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
  26.         test = True
  27.       ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
  28.         test = True
  29.       End If
  30.       If test Then 
  31.         rs.MoveLast
  32.       Else
  33.         rs.MoveNext
  34.       End If
  35.     Loop
  36.  
  37.     If test=False Then
  38.       MsgBox ("Time is available")
  39.     Else
  40.       MsgBox ("Time is unavailable")
  41.       Me.ScheduleStartTime = Null
  42.       Me.ScheduleEndTime = Null
  43.     End If
  44.   End If
  45.  
  46.   rs.Close
  47.   Set rs=Nothing
  48.   Set db=Nothing
  49.  
  50. End Sub
Feb 20 '07 #15

P: 16
Thanks for your continued help.

Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  3.        "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
  6.  
is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."

Expand|Select|Wrap|Line Numbers
  1. rs.Close
  2.  
is getting an error of "run time error 91, object variable or With block variable not set."

John
Feb 20 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for your continued help.

Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM tblSchedule INNER JOIN tblScheduleDetails " & _
  3.        "ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        " AND [ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate])
  6.  

is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."
Try running this query on it's own but make sure the form is opened in the background. First be sure to chang [MainForm] to the name of your main form. Let me know what happens.

Expand|Select|Wrap|Line Numbers
  1. SELECT [ScheduleStartTime], [ScheduleEndTime] 
  2. FROM tblSchedule INNER JOIN tblScheduleDetailsON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID 
  3. WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
  4. AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]


Expand|Select|Wrap|Line Numbers
  1. rs.Close
  2.  
is getting an error of "run time error 91, object variable or With block variable not set."

John
Change the last few lines of code from this ...

Expand|Select|Wrap|Line Numbers
  1.      End If
  2.   End If
  3.  
  4.   rs.Close
  5.   Set rs=Nothing
  6.   Set db=Nothing
  7.  
  8. End Sub
  9.  
to this ...

Expand|Select|Wrap|Line Numbers
  1.      End If
  2. rs.Close
  3.   Set rs=Nothing
  4.   Set db=Nothing
  5.    End If
  6.  
  7. End Sub
  8.  
Feb 20 '07 #17

P: 16
I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".

John
Feb 20 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".

John
Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT [ScheduleStartTime], [ScheduleEndTime] 
  2. FROM tblSchedule INNER JOIN tblScheduleDetails
  3. ON tblSchedule.[ScheduleID]=tblScheduleDetails.[ScheduleID]
  4. WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
  5. AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]
  6.  
There were a couple of spacing errors
Feb 20 '07 #19

P: 16
I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.

John
Feb 20 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.

John
That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.

Mary
Feb 20 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.

Mary
You could try this test.

Create a new query in access design view and add the tblSchedule and tblSchedule details. Then just drag down the appropriate fields and change the view to SQL query view and see what might be going on with the table name.
Feb 20 '07 #22

P: 16
Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.

Thanks
John
Feb 20 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.

Thanks
John
Did you move the code to the After Update event of the ScheduleStartTime control on the form?

Mary
Feb 20 '07 #24

P: 16
Yes i moved it to the after update event. I am still getting this error "run time error 2465, MS access can not find the field 'I' referred to in your expression."

John
Feb 20 '07 #25

P: 16
Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John
Feb 20 '07 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John
Put this in the After Update event of the ScheduleEndTime control

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleEndTime_AfterUpdate()
  2. Dim db as DAO.Database
  3. Dim rs as DAO.Recordset
  4.     Dim strMessage As String
  5. Dim startTime As Date
  6. Dim endTime As Date
  7. Dim test As Boolean
  8.  
  9.   test = False
  10.   If Me.NewRecord = True Then
  11.     set db = CurrentDb
  12.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  13.        "FROM Schedule INNER JOIN tblScheduleDetails " & _
  14.        "ON Schedule.ScheduleID=tblScheduleDetails.Schedule    ID " & _
  15.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  16.        " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
  17.  
  18.     startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
  19.     endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
  20.     If rs.RecordCount=0 Then Exit Sub
  21.     rs.MoveFirst
  22.     Do Until rs.EOF
  23.       If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
  24.         test = True
  25.       ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
  26.         test = True
  27.       ElseIf  endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
  28.         test = True
  29.       End If
  30.       If test Then 
  31.         rs.MoveLast
  32.       Else
  33.         rs.MoveNext
  34.       End If
  35.     Loop
  36.  
  37.     If test=False Then
  38.       MsgBox ("Time is available")
  39.     Else
  40.       MsgBox ("Time is unavailable")
  41.       Me.ScheduleStartTime = Null
  42.       Me.ScheduleEndTime = Null
  43.     End If
  44.  
  45.     rs.Close
  46.     Set rs=Nothing
  47.     Set db=Nothing
  48.  
  49.   End If
  50.  
  51. End Sub
  52.  
Feb 20 '07 #27

P: 16
Sorry been away for a few days.
For this code:
Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM Schedule INNER JOIN ScheduleDetails " & _
  3.        "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        "AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
  6.  
I am getting an error of "run time error 2465, MS access cant find the field 'I' referred to in your expression."

While i was away i created a new query that showed the double bookings. Is there any way I can validate these fields using this query as data is entered.

Thanks
johnblack
Feb 27 '07 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
Check that all the field names are exactly correct and that the subform object is called BookingsSubform (you will find this by checking the object properties under other for Name).

If they are all correct then try this. I have made one minor change.

Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM Schedule INNER JOIN ScheduleDetails " & _
  3.        "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
  5.        "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
  6.  
Mary
Feb 28 '07 #29

P: 28
atc
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:
Expand|Select|Wrap|Line Numbers
  1. "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
  2.  
Any more ideas?
I could send you the database if you want.....
johnblack
Feb 28 '07 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:
Expand|Select|Wrap|Line Numbers
  1. "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
  2.  
Any more ideas?
I could send you the database if you want.....
johnblack
John

Why has your user profile changed? Which should I send a PM to?

Mary
Feb 28 '07 #31

P: 28
atc
Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John
Feb 28 '07 #32

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John
Since you are currently logged in as atc I'll send it there.

Mary
Feb 28 '07 #33

NeoPa
Expert Mod 15k+
P: 31,263
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line:
Expand|Select|Wrap|Line Numbers
  1. "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
  2.  
Any more ideas?
I could send you the database if you want.....
johnblack
That's because the previous line doesn't include a separator (space) after the previous control reference in the WHERE clause.
Feb 28 '07 #34

NeoPa
Expert Mod 15k+
P: 31,263
The code should instead read :
Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  2.        "FROM Schedule INNER JOIN ScheduleDetails " & _
  3.        "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  4.        "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & " " & _
  5.        "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
Feb 28 '07 #35

MMcCarthy
Expert Mod 10K+
P: 14,534
Good catch, how did I miss that? :D
Feb 28 '07 #36

P: 16
Mary did you receive the file in the end.
John
Feb 28 '07 #37

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary did you receive the file in the end.
John
No John the second email had no attachment either.

Mary
Mar 1 '07 #38

MMcCarthy
Expert Mod 10K+
P: 14,534
No John the second email had no attachment either.

Mary
There were a couple of problems John.

1. You were trying to refer to controls on SubformA from SubformB so you can't use the Me. you need the full reference.

2. The rs.MoveNext has to be removed from the IF statement as it runs regardless of the whether the test is true or not.

Code should now be as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleEndTime_AfterUpdate()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim strMessage As String
  5. Dim startTime As Date
  6. Dim endTime As Date
  7. Dim test As Boolean
  8.  
  9.   test = False
  10.   If Me.NewRecord = True Then
  11.     Set db = CurrentDb
  12.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  13.             "FROM Schedule INNER JOIN ScheduleDetails " & _
  14.             "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  15.             "WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
  16.             "AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")
  17.  
  18.     startTime = Me.ScheduleStartTime
  19.     endTime = Me.ScheduleEndTime
  20.     If rs.RecordCount = 0 Then Exit Sub
  21.     rs.MoveFirst
  22.     Do Until rs.EOF
  23.         If startTime = rs!ScheduleStartTime Or endTime = rs!ScheduleEndTime Then
  24.           test = True
  25.         ElseIf startTime > rs!ScheduleStartTime And startTime < rs!ScheduleEndTime Then
  26.           test = True
  27.         ElseIf endTime < rs!ScheduleEndTime And endTime > rs!ScheduleStartTime Then
  28.           test = True
  29.         End If
  30.         If test Then
  31.           rs.MoveLast
  32.         End If
  33.         rs.MoveNext
  34.     Loop
  35.  
  36.     If test = False Then
  37.       MsgBox ("Time is available")
  38.     Else
  39.       MsgBox ("Time is unavailable")
  40.       Me.ScheduleStartTime = Null
  41.       Me.ScheduleEndTime = Null
  42.     End If
  43.  
  44.     rs.Close
  45.     Set rs = Nothing
  46.     Set db = Nothing
  47.  
  48.   End If
  49.  
  50. End Sub
  51.  
Mary
Mar 1 '07 #39

P: 28
atc
Thanks so much it works like a charm. Would it be possible for you to try and solve one other small problem. At the moment I have this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate(Cancel As Integer)
  2.   Dim strWhere As String
  3.   Dim varKey As Variant
  4.  
  5.   strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
  6.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  7.   varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  8.                    "[Courts]", _
  9.                    strWhere)
  10.   If Not IsNull(varKey) Then
  11.     Cancel = True
  12.     Call MsgBox(strWhere & vbCrLf & varKey)
  13.     Debug.Print strWhere & vbCrLf & varKey
  14.   End If
  15.  
  16. End Sub
  17.  
On the bookingsubform. This is to try and stop the same court and scheduledate being repeated in different records. Any ideas what is wrong?
ATC
Mar 1 '07 #40

P: 28
atc
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
Mar 1 '07 #41

MMcCarthy
Expert Mod 10K+
P: 14,534
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
Try switching it to the Before Update event.

Mary
Mar 1 '07 #42

NeoPa
Expert Mod 15k+
P: 31,263
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
You haven't said what is going wrong with this but, as Mary says, there is no Cancel parameter in the AfterUpdate event.
The idea is that you check the data before updating and, optionally, cancel the update. It would not make too much sense to try this after the update has already been applied.
Mar 1 '07 #43

P: 28
atc
Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC
Mar 1 '07 #44

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC
Try this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleDate_AfterUpdate()
  2.    If Not IsNull(Me.CourtID) Then
  3.       CheckDate
  4.    End If
  5. End Sub
  6.  
  7. Private Sub CourtID_AfterUpdate()
  8.    If Not IsNull(Me.ScheduleDate) Then
  9.       CheckDate
  10.    End If
  11. End Sub
  12.  
  13. Function CheckDate()
  14.   Dim strWhere As String
  15.   Dim varKey As Variant
  16.  
  17.   strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
  18.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  19.   varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  20.                    "[Courts]", _
  21.                    strWhere)
  22.   If Not IsNull(varKey) Then
  23.    Call MsgBox(strWhere & vbCrLf & varKey)
  24.    Debug.Print strWhere & vbCrLf & varKey
  25.    Me.ScheduleDate = Null
  26.   End If
  27.  
  28. End Function
  29.  
Mary
Mar 1 '07 #45

P: 28
atc
Thanks for your continued help.
With this code is place (entering the data courtID of 1 and a date of 01/03/2007 - not a duplicate) I get the same message as before but when I click ok it keeps the courtID of the new record, deletes the schedule date and moves to courtID of a new record below.
I also get the same result if the data is a duplicate.
ATC
Mar 1 '07 #46

NeoPa
Expert Mod 15k+
P: 31,263
John,
This is not really the thread to discuss this code.
I suggested that code in a completely different thread and half of it is simply to give debug information. It is not finished code. To work with someone else on this code is not good. How do you expect me to progress it with you if you get changes from outside sources.
If you want me to stop then please just let me know, otherwise I would appreciate being given the chance to deal with it without extraneous influences complicating matters even further (back in the original thread). Quite apart from anything else, the original thread will be no use as a reference if it stops dead in it's tracks half-done.
Mar 1 '07 #47

P: 28
atc
Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john
Mar 1 '07 #48

NeoPa
Expert Mod 15k+
P: 31,263
Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john
Thank you.
(Stop Duplicates) in case anyone wants to come over.
It's probably best though, within such a tight debugging loop as this one, that the two of us focus together unless and until we find we can't sort it. Extra heads in a situation like this is more likely to distract than help.
Mar 1 '07 #49

MMcCarthy
Expert Mod 10K+
P: 14,534
LOL - Are you calling me a distraction Ade.

John

In this case he is quite correct as I was unaware of the other thread I was missing half the information. NeoPa will call me in if he needs any futher assistance although I doubt it. He's really quite good just don't tell him I said that.

Mary
Mar 1 '07 #50

50 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.