Double Booking Problem

[johnblack]

Thanks for your continued help.

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

is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."

rs.Close

is getting an error of "run time error 91, object variable or With block variable not set."

John

[MMcCarthy]

Thanks for your continued help.

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

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.

SELECT [ScheduleStartTime], [ScheduleEndTime] 
FROM tblSchedule INNER JOIN tblScheduleDetailsON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID 
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]

rs.Close

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

	 End If
  End If

  rs.Close
  Set rs=Nothing
  Set db=Nothing

End Sub

to this ...

	 End If
rs.Close
  Set rs=Nothing
  Set db=Nothing
   End If

End Sub

[johnblack]

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

[MMcCarthy]

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

SELECT [ScheduleStartTime], [ScheduleEndTime] 
FROM tblSchedule INNER JOIN tblScheduleDetails
ON tblSchedule.[ScheduleID]=tblScheduleDetails.[ScheduleID]
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID] 
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]

There were a couple of spacing errors

[johnblack]

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

[MMcCarthy]

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

[MMcCarthy]

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.

[johnblack]

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

[MMcCarthy]

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 ScheduleStartTi me control on the form?

Mary

[johnblack]

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

[johnblack]

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

[MMcCarthy]

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

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 tblScheduleDetails " & _
	   "ON Schedule.ScheduleID=tblScheduleDetails.Schedule    ID " & _
	   "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
	   " AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"

	startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
	endTime = Me.[BookingsTimeSubform].[Form]![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
	  Else
		rs.MoveNext
	  End If
	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

[johnblack]

Sorry been away for a few days.
For this code:

    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] & "#")

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

[MMcCarthy]

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.

    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] & "#")

Mary

[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:

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

Any more ideas?
I could send you the database if you want.....
johnblack