How to prevent overlapping schedules?

[NeoPa]

Aah, when I said test data, I should have mentioned I was talking about the data you have in the Events table.
Assuming it's not private, just dump it in a post & I will import it in OK I'm sure.

PS. If you do make changes to protect the innocent, please test that the new data still suffers from the same problem before posting it ;)

[NeoPa]

The download worked, but there were references expected which I didn't have so the code wouldn't run for me.

However, the problem was obvious as you have no data in your Events table for Buildings (ServiceCenter in the db downloaded). As soon as you filter on that there will be no matching results.

I just reread this in the course of looking up what I knew about your current problem, and noticed it sounded like I was saying you were stupid to miss it.

That was not my intention. What I should have said was :

...
However, when I could see the whole situation the problem was clear to me, as you have no data in your Events table for Buildings (ServiceCenter in the db downloaded). ...

[Wiretwisterz]

I hope this was what you were looking for, if not, let me know, I'll resubmit a different way.

Thank you

Name	Status	Location	Start Date	End Date	Start Time	End Time	SubProcesses	Event Description	Notes	ID	Processes	ServiceCenter
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/18/2008	3/18/2008	9:30 AM	10:30 AM				266		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/25/2008	3/25/2008	9:30 AM	10:30 AM				267		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	4/1/2008	4/1/2008	9:30 AM	10:30 AM				268		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/19/2008	3/19/2008	2:30 PM	3:30 PM				271		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/26/2008	3/26/2008	2:30 PM	3:30 PM				272		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	4/2/2008	4/2/2008	2:30 PM	3:30 PM				273		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/14/2008	3/14/2008	9:30 AM	10:00 AM				285		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/28/2008	3/28/2008	9:00 AM	10:00 AM				286		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	3/31/2008	3/31/2008	8:30 AM	12:00 PM				304		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	4/28/2008	4/28/2008	8:30 AM	12:00 PM				305		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	5/19/2008	5/19/2008	8:30 AM	12:00 PM				307		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	6/30/2008	6/30/2008	8:30 AM	12:00 PM				308		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	7/28/2008	7/28/2008	8:30 AM	12:00 PM				309		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	8/25/2008	8/25/2008	8:30 AM	12:00 PM				310		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	9/29/2008	9/29/2008	8:30 AM	12:00 PM				311		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	10/27/2008	10/27/2008	8:30 AM	12:00 PM				312		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	11/24/2008	11/24/2008	8:30 AM	12:00 PM				313		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	12/29/2008	12/29/2008	8:30 AM	12:00 PM				314		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/17/2008	3/17/2008	9:00 AM	11:00 AM				321		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/21/2008	3/21/2008	9:00 AM	11:00 AM				322		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	4/1/2008	4/1/2008	10:30 AM	12:00 PM				326		Atlanta
TEST		Atlanta 4th Floor (Conference Room - 60 w/no tables)	3/18/2008	3/18/2008	10:31 AM	12:00 PM				327		Atlanta
TEST		Atlanta 5th Floor (Meeting Room - 10 w/table)	4/14/2008	4/14/2008	10:30 AM	12:00 PM				338		Atlanta
TEST		Atlanta 2nd Floor (Training Room -11 no tables)	3/18/2008	3/18/2008	10:00 AM	11:00 AM				363		Atlanta

[NeoPa]

That's perfect Wiretwister. I need to look at this at home, but that will upload fine for me.

As before, if I haven't replied by tomorrow, please bump the thread.

[NeoPa]

Something to think about in the mean-time is what you intend to do with your time fields. The existing code only works if you assume that the time element is included in the date. The data that you just posted (and the table layout) indicate this is not so.

I think the best way to handle this is to update the [...Date] field when the date or time is amended to reflect both (Date/Time).

[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 "txtStartTi me" 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).