How to prevent overlapping schedules?

[Wiretwisterz]

I am using Microsoft Office XP and I have developed an Access database that is used to schedule conference and training rooms within buildings.

I have one main form named "Events" that is used to schedule these rooms.
The "Events" form has several fields on it such as "Building" which is a Combo box with the name "cmbBuilding". There's a field below "Building" named "Location" which is called "cmbLocation". The 2 fields Building and Location are related because when someone chooses a buildling from the list, it modified what options are available in the Location drop down box since different buildings have different rooms on different floors. I have a Locations table, and a Buildings table, which the form uses to populate the drop down boxes.

Below the Location field is "cmbStartDate", "cmbEndDate", "txtStartTime", "txtEndTime" and other fields that are not related to my question.

The "Events" form updates an "Events" table which has these Field Names:
"Location", "Building", "StartDate", "EndDate", "StartTime", "EndTime".

I want to prevent locations from being double booked.

Example:
Person 1 schedules wants to reserve a room in Atlanta (cmbBuilding on form, Building on table). They choose Atlanta from the Building drop down box on the form and the Location drop down box below it gets modified to only show rooms in Atlanta. They then pick a date from a calendar control that populates the StartDate and EndDate fields on the form (cmbStartDate and cmbEndDate on the form but StartDate and EndDate on the table). The dates are autoformatted to this format : MM/DD/YYYY ... or M/D/YYYY if the month and day happen to be less than 10 (in other words, it doesn't put in the leading 0 if less than 10 e.g. not 01/03/2008, simply 1/3/2008). They then enter a StartTime and EndTime (txtStartTime and txtEndTime on the form, StartTime and EndTime on the table).

So... Atlanta - 5th Floor - 3/23/2008 through 3/25/2008 from 09:00am untill 01:00pm on each of those days.

If this reservation was made, I don't want anyone to be able to schedule a date for Atlanta on the 5th floor, between 3/23/2008 and 3/25/2008 between the hours of 9 am to 1pm. However they should still be able to schedule a meeting from 8am to 9am on between 3/23/2008 and 3/25/2008, and they should also still be able to schedule something after 1pm on 3/23/2008 through 3/25/2008.

I reviewed the discussion on these forums at the following pages:
http://www.thescripts.com/forum/thread604404.html
http://www.thescripts.com/forum/thread607917.html

And thought it might be possible to modify the code they suggested that would help him in his situation:

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]=" & 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
 
 

They were instructed to put this code into the AfterUpdate event of his EndDate field on his form I believe, which would be ok with me as well, as long as it would work. I do not have any subforms as that person in the links, and he was only using one Date field, which I wouldn't necessarily be opposed to if it made things any easier.

Please help if at all possible.

Thank You

[NeoPa]

Having now looked at the issue you report it seems that this is in fact your problem.

The current code only checks for the dates. The time fields are ignored as they are not included in your Date/Time fields.

If you try to add an item for a date that doesn't match any of your existing events, does it work as expected then?

If so, we know we are on the right track.

[NeoPa]

If you absolutely have to keep the dates and times separate, you can use the following code :

...
  Dim strFilter As String, strFormat As String
 
  strFormat = "m/d/yyyy HH:nn:ss"
  strFilter = "(([Building]='%B') AND " & _
              "([Location]='%L') AND " & _
              "([StartDate]+[StartTime]<=#%E#) AND " & _
              "([EndDate]+[EndTime]>=#%S#))"
  strFilter = Replace(strFilter, "%B", Me.Building)
  strFilter = Replace(strFilter, "%L", Me.cmbLocation)
  strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, strFormat))
  strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, strFormat))
 
  If DCount("[StartDate]", "[Events]", strFilter) > 0 Then
    Call MsgBox("Selected Range overlaps an existing range in the table")
    Cancel = True
  End If

Please note :
This is certainly not an approach I would recommend.

[Wiretwisterz]

I could not get the above posted code to produce the "Could not schedule" msg in any way, regardless of what I was scheduling.

[NeoPa]

Do you have any understanding as to where or why it was failing?

Have you ever tried using the debugging facilities (Debugging in VBA)?

[Wiretwisterz]

I read through your posts regarding each of the available windows and their possible uses when trying to figure out what your code is doing, however after adding the code to display error codes in the Immediate pane, nothing appears, and there are no errors produced that I can see. It simply allows the record to be scheduled regardless of conflict.

[NeoPa]

In the margin of the debug window, click on the DCount() line (14 in posted code) to toggle a breakpoint.

When the code stops here, type in, in the Immediate pane (Ctrl-G) :

?strFilter & " - " & DCount("[StartDate]", "[Events]", strFilter)

...and see what is displayed in the line below it.

[Wiretwisterz]

(([ServiceCenter]='Atlanta') AND ([Location]='Atlanta 4th Floor (Conference Room - 60 w/no tables)') AND ([StartDate]+[StartTime]<=#3/17/2008 00:00:00#) AND ([EndDate]+[EndTime]>=#3/17/2008 00:00:00#)) - 0
 
 

[NeoPa]

There we go :)

I didn't include any reference in the code to the two time controls on the form. I assume you have something like Me.cmbStartTime & Me.cmbEndTime as ComboBox controls where the operator selects the times?

If you can confirm this and provide me with the names of the two contols I can post a new version of the code that should work.

This is still not a way I would recommend, but it can be made to work :)

[Wiretwisterz]

The names of the two fields for time are "txtStartTime" and "txtEndTime".

I'd still like to use this way currently but I am open to reorganizing the database in a different manner if it would help in the long run. Could you please suggest any changes that could be made?

[NeoPa]

Thanks for the control names and please see post #50 for the recommendation I had of how it could be done better. This would apply to the set of Date/Time fields in the table as well as the controls on the form.

AfterUpdate procedures can be used to prepare other controls which contain the full Date/time value. These can be hidden if required.

I will look at the code for the immediate problem (a bit later now).

[Wiretwisterz]

So you're saying the time fields should be appended to the end of the date field(s)?

Sorry, trying to make sure I understand what it was you're suggesting.

Thanks,

[NeoPa]

Dim strFilter As String, strFormat As String
Dim datStartEnd As Date
 
strFormat = "m/d/yyyy HH:nn:ss"
strFilter = "(([Building]='%B') AND " & _
            "([Location]='%L') AND " & _
            "([StartDate]+[StartTime]<=#%E#) AND " & _
            "([EndDate]+[EndTime]>=#%S#))"
strFilter = Replace(strFilter, "%B", Me.Building)
strFilter = Replace(strFilter, "%L", Me.cmbLocation)
datStartEnd = CDate(Me.cmbEndDate & " " & Me.txtEndTime)
strFilter = Replace(strFilter, "%E", Format(datStartEnd, strFormat))
datStartEnd = CDate(Me.cmbStartDate & " " & Me.txtStartTime)
strFilter = Replace(strFilter, "%S", Format(datStartEnd, strFormat))
 
If DCount("[StartDate]", "[Events]", strFilter) > 0 Then
Call MsgBox("Selected Range overlaps an existing range in the table")
Cancel = True
End If

[NeoPa]

So you're saying the time fields should be appended to the end of the date field(s)?

Sorry, trying to make sure I understand what it was you're suggesting.

Thanks,

Essentially yes.

Have a look at how I've done it in the code and see if that makes sense to you.