By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,675 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,675 IT Pros & Developers. It's quick & easy.

How to prevent overlapping schedules?

P: 27
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:

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Dim strMessage As String
  4. Dim StartTime As Date
  5. Dim EndTime As Date
  6. Dim test As Boolean
  7.  
  8. test = False
  9.  
  10. If Me.NewRecord = True Then
  11.  
  12.     Set db = CurrentDb
  13.     Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
  14.                               "FROM Schedule INNER JOIN ScheduleDetails " & _
  15.                               "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  16.                               "WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & " " & _
  17.                               "AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
  18.  
  19.     StartTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
  20.  
  21.     EndTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
  22.  
  23.     If rs.RecordCount = 0 Then Exit Sub
  24.  
  25.         rs.MoveFirst
  26.  
  27.     Do Until rs.EOF
  28.  
  29.         If StartTime = rs!ScheduleStartTime Or EndTime = rs!ScheduleEndTime Then
  30.  
  31.                 test = True
  32.  
  33.                     ElseIf StartTime > rs!ScheduleStartTime And StartTime < rs!ScheduleEndTime Then
  34.  
  35.                         test = True
  36.  
  37.                     ElseIf EndTime < rs!ScheduleEndTime And EndTime > rs!ScheduleStartTime Then
  38.  
  39.                         test = True
  40.  
  41.         End If
  42.  
  43.  
  44.                     If test Then
  45.  
  46.                         rs.MoveLast
  47.  
  48.                     Else
  49.  
  50.                         rs.MoveNext
  51.  
  52.                     End If
  53.  
  54.     Loop
  55.  
  56.                     If test = False Then
  57.  
  58.                         MsgBox ("Time is available")
  59.  
  60.                     Else
  61.  
  62.                         MsgBox ("Time is unavailable")
  63.                         Me.ScheduleStartTime = Null
  64.                         Me.ScheduleEndTime = Null
  65.  
  66.                     End If
  67.  
  68.     rs.Close
  69.  
  70.     Set rs = Nothing
  71.  
  72.     Set db = Nothing
  73.  
  74.   End If
  75.  
  76.  
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
Mar 4 '08 #1
Share this Question
Share on Google+
62 Replies


P: 27
Does anyone have any suggestions, please?
Mar 4 '08 #2

P: 27
I would appreciate any guidance that could be provided. Even if it's simply saying that you don't think the code would work for my situation, so that I might try looking in another direction.

Thank You,
Mar 5 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. Your code logic seems OK in that it does check for start and end times that overlap another booking. You have some redundancy in your IF..ELSEIF part which can be simplified to

Expand|Select|Wrap|Line Numbers
  1. If ((StartTime >= rs!ScheduleStartTime) AND  (StartTime <= rs!ScheduleEndTime)) OR _
  2.    ((EndTime >= rs!ScheduleStartTime) AND (EndTime <= rs!ScheduleEndTime)) Then
  3.        test = True      
  4. End If
You do seem to be on the right track and should try your solution out.

-Stewart
Mar 5 '08 #4

P: 27
That's the problem, I'm not really sure how to modify the SQL statement at the beginning to fit my database. I'd appreciate it if anyone could explain which fields go into the SQL statement.
Mar 5 '08 #5

P: 27
This is the code modified to my best guess as to what should work with my database.
Expand|Select|Wrap|Line Numbers
  1. ("SELECT [txtStartTime], [txtEndTime] " & _
  2.                               "FROM Events INNER JOIN ScheduleDetails " & _
  3.                               "ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
  4.                               "WHERE [CourtID]=" & Me.[cmbDate]![CourtID] & " " & _
  5.                               "AND [cmbDate]=#" & Me.[cmbDate]![ScheduleDate] & "#")
I don't have any idea how to modify this part of the SQL query to fit my database because I don't understand the syntax. I don't have a subform on my form so I am guessing that I could just reference my form as in Me.[cmbDate]
Mar 5 '08 #6

P: 27
Can anyone help guide me in the correct direction please?

Thank You,
Mar 5 '08 #7

NeoPa
Expert Mod 15k+
P: 31,530
I'm afraid the length and complexity of your original post will frighten off all but the hardiest of members, who happen to have vast amounts of spare time to devote to your problem.

It's really down to you to break your questions up into palatable chunks. We're all volunteers here and, as such, give what we can. To devote that proportion of our spare time to one question would be a poor way to manage the resources.

That is not to say that any member SHOULD not answer your question. Far from it. But we do have guidelines posted as to how questions should prepared and also what you can and can't expect to be done for you.
Mar 5 '08 #8

NeoPa
Expert Mod 15k+
P: 31,530
Having said that, I can probably help a bit with the SQL if you can explain succinctly what you hope to achieve.
Mar 5 '08 #9

P: 27
In short, I want to check either before the user leaves the EndTime field or after the EndTime field updates, for whether or not there is already a scheduled event for the time they are requesting, or whether or not the time they are requesting falls between an already scheduled time
Mar 5 '08 #10

NeoPa
Expert Mod 15k+
P: 31,530
So, you have a form presumably. Are you wanting to check that a potential new added record will not overlap with an existing record in the same table? If so, are there any other criteria that need to be taken into consideration when checking against the table?
Some meta-data for the table is almost certain to help at some point too. Here is an example of how to post table meta-data :
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblStudent]
  2. Field; Type; IndexInfo
  3. StudentID; AutoNumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Mar 6 '08 #11

P: 27
The information you requested was posted in my original post, but not in that format. I will attempt to repost in your requested format.
Expand|Select|Wrap|Line Numbers
  1. Table Name=[Events]
  2. Field; Type; IndexInfo
  3. StartDate; Date/Time
  4. EndDate; Date/Time
  5. StartTime; Date/Time
  6. EndTime; Date/Time
  7. Location; Text
  8. Building; Text
I am unsure what the Index Info means, so I didn't not include it.

These are the relevant fields of the table to have the data checked against.
Mar 6 '08 #12

NeoPa
Expert Mod 15k+
P: 31,530
...
I am unsure what the Index Info means, so I didn't not include it.
...
PK = Primary Key
the main unique index for the table.
FK = Foreign Key
A link into another table via its PK (or possibly another unique key).

PS. There were a couple of important questions in my last post that might help us to proceed.
When I have answers to them I will be curious to know why you have separate date & time fields in your table, bearing in mind that an Access Date/Time field can store data for both?
Mar 6 '08 #13

P: 27
The only primary key in the Events tables is an autonumber field.

Data is entered into the table via a main form named Events.

I want to check that the new record being added via the form does not overlap with an existing record in the Events table.

I think the reason I had them seperated was becuase the StartDate and EndDate fields on the form are populated by a Calendar Control on the form also. The user clicks a date on the calendar control and it enters the date. The only real reason I did this was becuase it seemed the easist way for me at the time. There is also no real need for the EndDate field on the form or table if it would make it any easier determining if data overlapps. The only reason I added the EndDate field was if a room was scheduled for multiple days in a row.
Mar 6 '08 #14

NeoPa
Expert Mod 15k+
P: 31,530
The only primary key in the Events tables is an autonumber field.
They're generally important to include ;) We may get away without one this time.
Data is entered into the table via a main form named Events.

I want to check that the new record being added via the form does not overlap with an existing record in the Events table.
This is mainly what I needed to know (and is good news).
I think the reason I had them seperated was becuase the StartDate and EndDate fields on the form are populated by a Calendar Control on the form also. The user clicks a date on the calendar control and it enters the date. The only real reason I did this was becuase it seemed the easist way for me at the time. There is also no real need for the EndDate field on the form or table if it would make it any easier determining if data overlapps. The only reason I added the EndDate field was if a room was scheduled for multiple days in a row.
We can do date ranges if we have to. What is more fiddly is creating Date / Time fields from separate (fields) date & time info.
Assuming then, TextBoxes on the form called txtStartDate & txtEndDate (with no time elements), we would have something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strFilter As String
  3.  
  4.   strFilter = "(([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  5.   strFilter = Replace(strFilter, "%E", Format(Me.txtEndDate, "m/d/yyyy"))
  6.   strFilter = Replace(strFilter, "%S", Format(Me.txtStartDate, "m/d/yyyy"))
  7.   If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
  8.     Call MsgBox("Selected Range overlaps with existing range in the table")
  9.     Cancel = True
  10.   End If
  11. End Sub
Mar 7 '08 #15

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strFilter As String
  3.  
  4.   strFilter = "(([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  5.   strFilter = Replace(strFilter, "%E", Format(Me.txtEndDate, "m/d/yyyy"))
  6.   strFilter = Replace(strFilter, "%S", Format(Me.txtStartDate, "m/d/yyyy"))
  7.   If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
  8.     Call MsgBox("Selected Range overlaps with existing range in the table")
  9.     Cancel = True
  10.   End If
  11. 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
Mar 7 '08 #16

NeoPa
Expert Mod 15k+
P: 31,530
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.
Mar 7 '08 #17

FishVal
Expert 2.5K+
P: 2,653
Oopss. How could I overlook that?!
Really nice solution, I appreciate it.

Best regards,
Fish.
Mar 7 '08 #18

NeoPa
Expert Mod 15k+
P: 31,530
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.
Mar 7 '08 #19

P: 27
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 "txtStartDate" to "cmbEndDate" and "cmbStartDate".

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.   Dim strFilter As String
  4.  
  5.   strFilter = "(([StartDate]<=#%E#) AND ([EndDate]>=#%S#))"
  6.   strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, "m/d/yyyy"))
  7.   strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, "m/d/yyyy"))
  8.   If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
  9.     Call MsgBox("Selected Range overlaps with existing range in the table")
  10.     Cancel = True
  11.   End If
  12.  
  13. End Sub
  14.  
Mar 7 '08 #20

NeoPa
Expert Mod 15k+
P: 31,530
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strFilter As String
  3.  
  4.   strFilter = "(([Building]='%B') AND ([Location]='%L') AND " & _
  5.               "([StartDate]<=#%E#) AND ([EndDate]>=#%S#))"
  6.   strFilter = Replace(strFilter, "%B", Me.cmbBuilding)
  7.   strFilter = Replace(strFilter, "%L", Me.cmbLocation)
  8.   strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, "m/d/yyyy"))
  9.   strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, "m/d/yyyy"))
  10.   If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
  11.     Call MsgBox("Selected Range overlaps an existing range in the table")
  12.     Cancel = True
  13.   End If
  14. 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?
Mar 7 '08 #21

NeoPa
Expert Mod 15k+
P: 31,530
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).
Mar 7 '08 #22

P: 27
Aren't the greater than and less than operands switched?
Mar 8 '08 #23

NeoPa
Expert Mod 15k+
P: 31,530
That depends on what you mean ;)
Line #10 should read :
Expand|Select|Wrap|Line Numbers
  1. If DCount("[StartDate]", "[Events]", strFilter) > 0 Then
rather than :
Expand|Select|Wrap|Line Numbers
  1. If DCount("[StartDate]", "[Events]", strFilter) < 1 Then
Mar 8 '08 #24

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

Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.  
  3.     strFilter = "(([Building]='%B') AND ([Location]='%L') AND " & _
  4.                 "([StartDate]<=#%E#) AND ([EndDate]>=#%S#))"
  5.     strFilter = Replace(strFilter, "%B", Me.Building)
  6.     strFilter = Replace(strFilter, "%L", Me.cmbLocation)
  7.     strFilter = Replace(strFilter, "%E", Format(Me.cmbEndDate, "m/d/yyyy"))
  8.     strFilter = Replace(strFilter, "%S", Format(Me.cmbStartDate, "m/d/yyyy"))
  9.  
  10.     If DCount("[StartDate]", "[Events]", strFilter) > 0 Then
  11.  
  12.     Call MsgBox("Selected Range overlaps an existing range in the table")
  13.  
  14.     Cancel = True
  15.   End If
  16.  
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.
Mar 11 '08 #25

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

Thank You
Mar 11 '08 #26

NeoPa
Expert Mod 15k+
P: 31,530
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 :
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(strFilter)
... after each line where you change the value (3, 5, 6, 7 & 8).
An alternative of :
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 11 '08 #27

NeoPa
Expert Mod 15k+
P: 31,530
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.
Mar 11 '08 #28

Expert 5K+
P: 8,434
Subscribing .
Mar 12 '08 #29

P: 27
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbBuilding_AfterUpdate()
  2.     cmbLocation.Requery
  3.     cmbLocation = cmbLocation.ItemData(0)
  4. End Sub
The cmbLocation combo box on my form has no "Default Value" set. Its RowSourceType is "Table/Query" and its rowsource is:
Expand|Select|Wrap|Line Numbers
  1. "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:
Expand|Select|Wrap|Line Numbers
  1. IndexName      Field Name  Sort Order
  2. DateTimeCheck  StartDate   Ascending
  3.                StartTime   Ascending
  4.                Location    Ascending
  5.  
  6. PrimaryKey     ID          Ascending
Below is the output from the debugging code that you requested.
Expand|Select|Wrap|Line Numbers
  1. (([Building]='%B') AND ([Location]='%L') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  2.  
  3. (([Building]='Atlanta') AND ([Location]='%L') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  4.  
  5. (([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#%E#) AND ([EndDate]>=#%S#))
  6.  
  7. (([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#3/12/2008#) AND ([EndDate]>=#%S#))
  8.  
  9. (([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor') AND ([StartDate]<=#3/12/2008#) AND ([EndDate]>=#3/12/2008#))
  10.  
  11. DataErr =  3022 
  12. DataErr =  2169 
  13. DataErr =  2169 
  14.  
Mar 12 '08 #30

NeoPa
Expert Mod 15k+
P: 31,530
I'm a little confused.
Your answer tells me a great deal, but I can't see why the extra bits (([Building]='Atlanta') AND ([Location]='Atlanta 4th Floor')) would cause any problems unless you had no records that exactly matched (both of) those values in your [Events] table.

Line #9 of the Debug output indicates that the code has worked exactly as intended so no worries there. I'm not sure where the extra lines are coming from though (#11, #12 & #13). They don't seem to be connected to any code I've seen in here.
Mar 12 '08 #31

P: 27
May I e-mail you the DB and have you take a look?
Mar 12 '08 #32

NeoPa
Expert Mod 15k+
P: 31,530
I'd rather you attached a ZIPped copy to a post.

It can probably be arranged if necessary though. I don't want to give out my e-mail address publicly so I will need to arrange for it to be relayed somehow if we must go that route.
Mar 12 '08 #33

P: 27
Click

This should work, if it doesn't, let me know.
Mar 12 '08 #34

Expert 5K+
P: 8,434
"SELECT Location FROM Locations WHERE Building=cmbBuilding ORDER BY Location; "
I'm really curious. Can you actually code a control name in the SQL like this?
Mar 13 '08 #35

NeoPa
Expert Mod 15k+
P: 31,530
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.
Mar 13 '08 #36

NeoPa
Expert Mod 15k+
P: 31,530
I'm really curious. Can you actually code a control name in the SQL like this?
It seems so yes.
I must admit that I, like you, wouldn't have expected that to work, but hey.
Mar 13 '08 #37

Expert 5K+
P: 8,434
It seems so yes.
I must admit that I, like you, wouldn't have expected that to work, but hey.
I suppose it makes sense as long as you're working within Access, since it's effectively an interface glued onto the database engine. I'd be very surprised if you could do the same when talking to the database engine from another language such as VB6.

Of course it might be a different matter working within the .Net framework, but I wouldn't know.
Mar 13 '08 #38

P: 27
Ah, how stupid of me. I understand what the problem was, so I made sure to bind the field on the form correctly and updated the data in the table for ServiceCenter, but it's still not quite working properly for me.

It seems to still be having issues. It seems to detect overlapping schedules, but then after I add one good record, it detects every record after that as being invalid. I'm sorry I probably didn't make much sense, but I don't really know how to describe exactly what it's doing or why.
Mar 13 '08 #39

NeoPa
Expert Mod 15k+
P: 31,530
OK, this is going to be difficult as :
  1. I have to do it at home.
  2. The code doesn't compile at home so I can't easily test.
  3. I don't currently have a copy of your up-to-date data.
If you could attach a copy of your updated test data, then I can run a test on the SQL alone if you can explain exactly what you are attempting to do.
Mar 13 '08 #40

P: 27
The calendar control requires QuickTime to work, so if that's the problem you're having, you can install quicktime to fix it.

Here's an updated link to the info.

Click

In this database, there is already a record for a scheduled spot of:

ServiceCenter - Atlanta, Location - Atlanta 4th Floor, StartDate of 3/18/2008 , EndDate of 3/18/2008, StartTime of 9:30am, EndTime of 10:30am.

If I attempt to schedule a slot for the same service center, location, start date, end date, and a start time of 8:00am to 9:00am, it still produces the error of it overlapping even though it does not.
Mar 13 '08 #41

jaxjagfan
Expert 100+
P: 254
Is your organization using Outlook and Exchange as your email. Setup each location as tho it were an Outlook user and schedule meetings against those users as well as attendees. This will show users (rooms) availability and will be reflected immediately throughout the organization. I have worked for several Fortune 500 clients and that is the way it is done for most of them.

This works for meeting rooms, classrooms, and other organizational areas that can be set for scheduled use.

No applications to build and update.
Mar 13 '08 #42

P: 27
We use GroupWise, and it does indeed have a Calender system, but I'd really prefer to do it this way as I tend to learn much more and expand my knowledge a bit.

Thank you for the suggestion though, I appreciate it!
Mar 13 '08 #43

NeoPa
Expert Mod 15k+
P: 31,530
I stretch to looking at some poster's dbs sometimes, but I will certainly not be changing my configuration to comply with any requirements I'm afraid.

As I said, get the updated data to me & I will look at the particular problem(s) via the queries.
Mar 13 '08 #44

P: 27
Do you want the current debug info or am I misunderstanding which data you want?
Mar 13 '08 #45

NeoPa
Expert Mod 15k+
P: 31,530
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 ;)
Mar 13 '08 #46

NeoPa
Expert Mod 15k+
P: 31,530
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). ...
Mar 13 '08 #47

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

Thank you

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

NeoPa
Expert Mod 15k+
P: 31,530
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.
Mar 14 '08 #49

NeoPa
Expert Mod 15k+
P: 31,530
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).
Mar 14 '08 #50

62 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.