How to prevent overlapping schedules?

[FishVal]

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim strFilter As String

  strFilter = "(([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  strFilter = Replace(strFilter, "%E", Format(Me.txtEndDate, "m/d/yyyy"))
  strFilter = Replace(strFilter, "%S", Format(Me.txtStartDate, "m/d/yyyy"))
  If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
    Call MsgBox("Selected Range overlaps with existing range in the table")
    Cancel = True
  End If
End Sub

Hi, NeoPa

The criteria is incomplete. A given time interval overlaps with an existing interval if

• [Start] or [End] of given interval is between [Start] and [End] of an existing one
• both [Start] or [End] of given interval are outside [Start] and [End] of an existing one

Because of the specific situation - time bookings are repetitive in a given date interval, the same criteria has to be applied on date and on time interval.
And sure [Location] and [Building] fields should be the same too.

So the whole criteria for overlapping records will look like
(daterange overlapps) AND (timerange overlaps) AND ([Location] is the same) AND ([Building] is the same)

Regards,
Fish

[NeoPa]

I'm sorry Fish, but I think you've got that wrong.

My algorithm assumes Date/Time values or date values without time involved (as explained in my post - #15).
Furthermore it does handle ALL situations of any overlaps - check it out and you'll see it does work. A fuller breakdown of the logic can be found in a thread on a similar subject (Between And Operator).
Time values, if included, must be associated with the date value! It simply will not work correctly when checked separately as you have suggested.

The [Location] & [Building] fields may well come into it too, as you have suggested, but as I have not been told of that (after asking for relevant details to be expressed clearly) then I have not included it in my logic.

[FishVal]

Oopss. How could I overlook that?!
Really nice solution, I appreciate it.

Best regards,
Fish.

[NeoPa]

No worries my friend :)
These forums work as a place for discussion and argument as well as for disseminating help. It's quite right that you question things, otherwise errors may easily slip in.

[Wiretwisterz]

This is the code I put into the BeforeUpdate event for the Events form, but it didn't stop me from scheduling.

I changed the two fields in your original code rom "txtEndDate " and "txtStartDa te" to "cmbEndDate " and "cmbStartDa te".

As mentioned by you above, I guess I didn't specify that I also need it to check against the Location and Building fields on the form and in the table.

e.g.- Can't book same "Location" in the same "Building", on the same "Date" at the same "Time".

Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  Dim strFilter As String
 
  strFilter = "(([StartDate]<=#%E#) AND ([EndDate]>=#%S#))"
  strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, "m/d/yyyy"))
  strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, "m/d/yyyy"))
  If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
    Call MsgBox("Selected Range overlaps with existing range in the table")
    Cancel = True
  End If

End Sub

[NeoPa]

I will do a version with the [Building] & [Location] fields tested too, but this would not stop the scheduling from failing.
To see what's going wrong it would be a good idea to debug the code as it runs, seeing what the filter resolves to before it's applied, etc. If it fails to behave as expected, why?

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim strFilter As String

  strFilter = "(([Building]='%B') AND ([Location]='%L') AND " & _
              "([StartDate]<=#%E#) AND ([EndDate]>=#%S#))"
  strFilter = Replace(strFilter, "%B", Me.cmbBuilding)
  strFilter = Replace(strFilter, "%L", Me.cmbLocation)
  strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, "m/d/yyyy"))
  strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, "m/d/yyyy"))
  If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
    Call MsgBox("Selected Range overlaps an existing range in the table")
    Cancel = True
  End If
End Sub

Are all the ComboBoxes (or whatever controls are used) bound to the underlying record that's being added to the table by the form?

[NeoPa]

After reading over my last post, then rereading it a couple of times, I've just just realised what the reason is that the code is not working as expected. It's actually working perfectly, just not as intended. I'll give you some time to look over it and post here why you think it behaves as it does.

Essentially we're wasting our time if you don't learn something from this experience. If you don't manage to see the flaw by later in the weekend I will come back and post the answer.

PS. Try adding a range that should be ok (previously unused).

[Wiretwisterz]

Aren't the greater than and less than operands switched?

[NeoPa]

That depends on what you mean ;)
Line #10 should read :

If DCount("[StartDate]", "[Events]", strFilter) > 0 Then

rather than :

If DCount("[StartDate]", "[Events]", strFilter) < 1 Then

[Wiretwisterz]

Hmm, I got the original code without the Building and Location in it to work, but when I changed it to this:

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

It won't work. I've looked it over several times and I am still unable to realize my problem.

Could you please further explain exactly what this code does line by line so I may more completely understand what is going on here?

Thank you so much.

[Wiretwisterz]

Can anyone perhaps help me figure out what is not working please?

Thank You

[NeoPa]

I will help, but please be patient. This is after all a forum and not a support company.

I'm pressed for time at the moment (as I have been all day at work) so let me ask you to add the code :

Call MsgBox(strFilter)

... after each line where you change the value (3, 5, 6, 7 & 8).
An alternative of :

Debug.Print strFilter

... will show all the results together in the Immediate Pane (See Debugging in VBA). Look through these values and see if you can determine what is going on. Post what you discover and I will come back again as soon as I can and fill in any gaps.

[NeoPa]

When you see the results of what strFilter contains at the various stages, it may point you to the Me.Building & Me.Location ComboBoxes.

It may be interesting to know how they are defined. Are the default values of the ComboBoxes the text values referred to in your table MetaData? Or are they related indexes of some kind?

You will need to find that out before we can proceed further. I'd be interested to see the results of the Debug.Print lines when you get them. All those (debug type) lines can be deleted when the code is working by the way. They are simply there to help us to find what is going wrong.

[Killer42]

Subscribing .

[Wiretwisterz]

The cmbBuilding combo box on my form has no "Default Value" set. Its RowSourceType is "Table/Query" and its rowsource is a table named

"Buildings" . The cmbBuilding also has the following afterupdate event code:

Private Sub cmbBuilding_AfterUpdate()
    cmbLocation.Requery
    cmbLocation = cmbLocation.ItemData(0)
End Sub

The cmbLocation combo box on my form has no "Default Value" set. Its RowSourceType is "Table/Query" and its rowsource is:

"SELECT Location FROM Locations WHERE Building=cmbBuilding ORDER BY Location;  "

The cmbStarDate combo box on my form has no "Default Value" set.

The cmbEndDate combo box on my form has no "Default Value" set.

The txtStartTime text box on my form has no "Default Value" set.

The txtEndTime text box on my form has no "Default Value" set.

None of my fields in the Events table have the "Indexed" value set to yes except the primary key which is an autonumber.

On my Events table, which I click the Indexes button, the box that comes up looks like this:

IndexName      Field Name  Sort Order
DateTimeCheck  StartDate   Ascending
               StartTime   Ascending
               Location    Ascending

PrimaryKey     ID          Ascending

Below is the output from the debugging code that you requested.

(([Building]='%B') AND ([Location]='%L') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))

(([Building]='Atlanta') AND ([Location]='%L') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))

(([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))

(([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#3/12/2008#) AND ([EndDate]>=#%S#))

(([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#3/12/2008#) AND ([EndDate]>=#3/12/2008#))

DataErr =  3022 
DataErr =  2169 
DataErr =  2169