Double Booking Problem

[MMcCarthy]

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:

"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")

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

[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

[MMcCarthy]

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

[NeoPa]

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:

"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")

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.

[NeoPa]

The code should instead read :

Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
       "FROM Schedule INNER JOIN ScheduleDetails " & _
       "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
       "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & " " & _
       "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")

[MMcCarthy]

Good catch, how did I miss that? :D

[johnblack]

Mary did you receive the file in the end.
John

[MMcCarthy]

Mary did you receive the file in the end.
John

No John the second email had no attachment either.

Mary

[MMcCarthy]

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:

Private Sub ScheduleEndTime_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean

  test = False
  If Me.NewRecord = True Then
	Set db = CurrentDb
	Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
			"FROM Schedule INNER JOIN ScheduleDetails " & _
			"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
			"WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
			"AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")

	startTime = Me.ScheduleStartTime
	endTime = Me.ScheduleEndTime
	If rs.RecordCount = 0 Then Exit Sub
	rs.MoveFirst
	Do Until rs.EOF
		If startTime = rs!ScheduleStartTime Or endTime = rs!ScheduleEndTime Then
		  test = True
		ElseIf startTime > rs!ScheduleStartTime And startTime < rs!ScheduleEndTime Then
		  test = True
		ElseIf endTime < rs!ScheduleEndTime And endTime > rs!ScheduleStartTime Then
		  test = True
		End If
		If test Then
		  rs.MoveLast
		End If
		rs.MoveNext
	Loop

	If test = False Then
	  MsgBox ("Time is available")
	Else
	  MsgBox ("Time is unavailable")
	  Me.ScheduleStartTime = Null
	  Me.ScheduleEndTime = Null
	End If

	rs.Close
	Set rs = Nothing
	Set db = Nothing

  End If

End Sub

Mary

[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

Private Sub Form_AfterUpdate(Cancel As Integer)
  Dim strWhere As String
  Dim varKey As Variant

  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
    Cancel = True
    Call MsgBox(strWhere & vbCrLf & varKey)
    Debug.Print strWhere & vbCrLf & varKey
  End If

End Sub

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

[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

[MMcCarthy]

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

[NeoPa]

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.

[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

[MMcCarthy]

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...

Private Sub ScheduleDate_AfterUpdate()
   If Not IsNull(Me.CourtID) Then
	  CheckDate
   End If
End Sub

Private Sub CourtID_AfterUpdate()
   If Not IsNull(Me.ScheduleDate) Then
	  CheckDate
   End If
End Sub

Function CheckDate()
  Dim strWhere As String
  Dim varKey As Variant

  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
   Call MsgBox(strWhere & vbCrLf & varKey)
   Debug.Print strWhere & vbCrLf & varKey
   Me.ScheduleDate = Null
  End If

End Function

Mary