check time does not overlap

[shutzy]

where do i start!!!

in my form i want to check if the appointment does not overlap another appointment.

the appointment is made up of items that all have an individual time allocated.

i thought of doing something like this but i am useless with syntax etc.

if DSum("[TreatmentTime]", tblOrdersItems, OrderID " = & " "[Forms].[frmNewAppointment].[OrderID]") + "[Forms]![frmNewAppointment]![OrderTime]" > 'any record' tblOrdersItems.StartTime where StartDate = frmNewAppointment.OrderDate and Employee = frmAppointmentTreatmentItems.Employee then

msgbox. this appointment is too long.
etc
etc


the only thing is that i dont know if it will work. it does in my head.

TreatmentTime = Time Field
OrderID = Foreign Key Field
OrderTime = TimeField
StartDate = Date Field
Employee = Foreign Key Field

the table is a one to many (parent/child). the OrderID is the parent. i have used a DSum to calculate the duration and the '+' is to add the start time to the duration which should leave the finish time.

then the second part '>' to see if it is greater than any other start time.

in my theory this will work but i dont know if access will search all records that have the same date.

im only guessing at this so i am looking for a way to do it.

thanks

 

[jdraw]

see this for some ideas
http://baldyweb.com/OverLap.htm

 

[gemma-the-husky]

in order not to overlap, you need these

given a startand endtime -you cannot have another appointment with the starttime in between your times, or with the endtime inbetween your times.

this doesn't pick up the problem of an existing appointment that starts before your appointment and ends after.

so you also need to test for this special state.

 

[shutzy]

hi gemma. i have these spefications in my table. each OrderItemsID has a start time and duration. so start time + duration = finish time.

the other issue you raised i think is solved. i have a diary view that you dbl click to make a new appointment. each time 9:00, 9;15 etc is a text box and where an appointment already exists the dbl click will take you to a form where you can only view the existing appointment. where there is a blank space. ie no appointment it takes you to a form where you can then begin to build the appointment.

with jdraw's post at least i know that this is quite straight forward. so all i need to do now is find out how to check.

i only want to check against the appoitment being created at that time.

do you think my effort will work? if i can search all items that have the same date or do you think it will need a temp tbl from a query.

any suggestions?

 

[CJ_London]

Hi,

I often have to handle overlaps with time management and I use a function similar to the following which will handle both start and end time clashes:

Function ApptClash(ApptStart As Date, ApptTime As Date, Employee As Long) As Boolean
Dim Mydb As Database
Dim Rst As Recordset
Set Mydb = CurrentDb
Set Rst = Mydb.OpenRecordset("SELECT * FROM TreatmentTable WHERE Employee = " & Employee & " AND StartDate< " & ApptStart + ApptTime & " AND StartDate+OrderTime>" & ApptStart)
If Not Rst.EOF Then
MsgBox "Appointment clashes with one starting at " & Rst.Fields("startdate")
ApptClash = True
Else
ApptClash = False
End If
set rst=nothing
set mydb=nothing

End Function

The recordset will return more than one result if it clashes at both ends so should be called with a bit of VBA along the following lines

..capture new start time
..capture new ordertime
..capture employee
do while apptclash(newstarttime, newordertime,employee)
..capture new start time
..capture new ordertime
..capture employee
loop

Hope this helps, please tick the thanks box if it does!

 

[shutzy]

thanks cj. im not really a programmer. just a beginner. i can follow most of it and i can see the query that would bring up all records for the same date with the same employee that are greater than time. the only thing is i am not familiar with function, modules or public subs. i have only ever used private subs.

if i created a query that brought up the records and pasted the sql in instead of the sql you have provided would that work.

also where in my code window do i put it. i have a button that this will go behind but i have many lines of code before i want to check the time clash.

do i call the function?

 

[CJ_London]

Hi Shutzy,

Functions are just like private subs in respect of how they work, the only difference is that a sub completes a task whilst a function returns a value in this case a boolean or Yes/No.

The only difference between public and private is that public are made available to other modules whilst private are only available in the module in which it resides.

All code is held in modules, if it relates to a form then it will be in a class module connected to the form and will be private to that form and this is probably what you are using.

Assuming your form has no code at all at present then what you need to do is go to the form properties - events and for your purposes on this occasion you need to select the beforeupdate event.

In this event copy the following:

Cancel= apptclash(newstarttime, newordertime,employee) 'note: substitute the parameters for the names of the relevant controls in your form.

Then move to a line above the start of the sub (Private Sub Form_BeforeUpdate(Cancel As Integer)) and then copy the ApptClash function in its entirety - this will be a private function and can be used in this form and nowhere else.

To make sure this is correct, the apptclash you pasted into BeforeUpdate should now have changed to ApptClash (uppercase)

And that's it. Make sure you change the parameters to match your form. So what will happen is when a user tries to move to a new record or close the form, Access will try to update the record. If it finds there is a clash it will cancel the update and leave the user on the same record until a non-clashing appointment is made.

Without knowing more about your form it is difficult to advise further but give the above a go and see how you do!

Thanks

 

[shutzy]

i dont have the start time in my form. i an use update query to input the start time. i do this because i have many items to an appointment. so the times have to run in order. the rpoblem lies when and if i remove an item that is for example 3rd in the list of 5. all the times need to be updated. is it possible to have a DLookup in the function. i have the item duration in the form just not the start time. i was thinking of calling the function after the record has been created and the start time update has been done.

ive put the code below to show you(function call in ' ' )

Private Sub btnSaveAppointmentTreatmentItem_Click()
[Cost].Value = [txtTreatmentCost]
[ActualCost].Value = [txtTreatmentCost]
[TreatmentTime].Value = [comboTreatmentDuration]
If IsNull([comboEmployee]) Then
MsgBox "You Have Not Selected A Therapist", vbOKOnly, "No therapist selected!."
Exit Sub
End If
If IsNull([TreatmentItemsID]) Then
MsgBox "You Have Not Selected A Treatment", vbOKOnly, "No treatment selected!."
Exit Sub
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tblStartTimeUpdate (OrdersItemsID, StartTime )SELECT RunningSumTotal.OrdersItemsID, [RunningTotal]-[TreatmentTime] AS StartTime FROM RunningSumTotal")
DoCmd.RunSQL ("UPDATE tblOrdersItems INNER JOIN tblStartTimeUpdate ON tblOrdersItems.OrdersItemsID = tblStartTimeUpdate.OrdersItemsID SET tblOrdersItems.StartTime = [tblStartTimeUpdate]![StartTime]")
DoCmd.RunSQL ("DELETE tblStartTimeUpdate.OrderID AS Expr1 FROM tblStartTimeUpdate")
[lstCurrentlySelectedTreatments].Requery
'call function'
'msg explaing if the appointment clashes'
DoCmd.GoToRecord , , acNewRec
End Sub

if the appointment clashes then i was going to delete the record. im not very familiar with doing thing with records that arent saved. so i generally save the record then query them and if neccessary delete them.

what would you do?

 

[CJ_London]

Hi Shutzy,

Looks more complex than it needs to be so not sure if this will work but try this - the only things I'm not sure about is the table and field names in the function (highlighted orange) might be tblOrdersItems? which you will need to change

Function ApptClash(ApptStart As Date, ApptTime As Date, Employee As Long) As Boolean
Dim Mydb As Database
Dim Rst As Recordset
Set Mydb = CurrentDb
Set Rst = Mydb.OpenRecordset("SELECT * FROM TreatmentTable WHERE Employee = " & Employee & " AND StartTime< " & ApptStart + ApptTime & " AND StartTime+OrderTime>" & ApptStart)
If Not Rst.EOF Then
MsgBox "Appointment clashes with one starting at " & Rst.Fields("starttime")
ApptClash = True
Else
ApptClash = False
End If
set rst=nothing
set mydb=nothing

End Function

Private Sub btnSaveAppointmentTreatmentItem_Click()
[Cost].Value = [txtTreatmentCost]
[ActualCost].Value = [txtTreatmentCost]
[TreatmentTime].Value = [comboTreatmentDuration]
If IsNull([comboEmployee]) Then
MsgBox "You Have Not Selected A Therapist", vbOKOnly, "No therapist selected!."
Exit Sub
End If
If IsNull([TreatmentItemsID]) Then
MsgBox "You Have Not Selected A Treatment", vbOKOnly, "No treatment selected!."
Exit Sub
End If

 
'new code added here
 
If ApptClash([RunningTotal]-[TreatmentTime], [TreatmentTime], [comboEmployee]) =true then exit sub 

 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tblStartTimeUpdate (OrdersItemsID, StartTime )SELECT RunningSumTotal.OrdersItemsID, [RunningTotal]-[TreatmentTime] AS StartTime FROM RunningSumTotal")
DoCmd.RunSQL ("UPDATE tblOrdersItems INNER JOIN tblStartTimeUpdate ON tblOrdersItems.OrdersItemsID = tblStartTimeUpdate.OrdersItemsID SET tblOrdersItems.StartTime = [tblStartTimeUpdate]![StartTime]")
DoCmd.RunSQL ("DELETE tblStartTimeUpdate.OrderID AS Expr1 FROM tblStartTimeUpdate")
[lstCurrentlySelectedTreatments].Requery
'call function'
'msg explaing if the appointment clashes'
DoCmd.GoToRecord , , acNewRec
End Sub

 

[shutzy]

sorry, im not trying to pick holes in this but

StartTime+OrderTime

the ordertime is from tblOrders. im not exactly sure why you are calling this.
should it not be to get the finish time of the item. if this is what you are wanting

StartTime+TreatmentTime

the treatment time is the duration.

sorry it was poor naming at the start

also i am guessing that this function looks at the record start time and sees if it runs past another item. if this is the caase then should the sql not look at the dates. so the criteria would be

<StartTime
Employee=
OrderDate=

i put in the code. changed the TreatmentsTable to tblOrdersItems but it is cumming up with an error.

the next part i have notice is this

If ApptClash([RunningTotal]-[TreatmentTime], [TreatmentTime], [comboEmployee]) =true then

im not sure what the running total is. im not sure what it even does

thanks for your help so far. at least now i have put in a function even if i couldnt create one from scratch.

also im not trying to get you to do it for me im just trying to understand it so i can finess it by myself.

 

[CJ_London]

Hi Shutzy,

I'm happy to help - and you've now added a function!

I think we probably need to clarify the data a bit, I was making some assumptions from your original post. For example I assumed StartDate mentioned in your original post was StartTime mentioned later.

Also, I see the orange highlighting did not come out in the post so there are still some changes to be made to the function. Hopefully the below will help to explain the changes required

also i am guessing that this function looks at the record start time and sees if it runs past another item. if this is the caase then should the sql not look at the dates. so the criteria would be

Actually, its the other way round. What the SQL query in the function is doing is looking for any existing records which overlap the new record so the data needed to do this is the startdate/time plus the duration of the existing records and the startdate and duration of the new record (i.e. what is on the form)

With regards the existing records
The TreatmentTable is the table which stores all the appointments - from your code it looks like it is tblOrdersItems which is updated with the start time or maybe TblOrders mentioned in your last post. I'm assuming that the duration and employee are also stored in this table so you can substitute Employee in 'WHERE Employee = ' with the name of the employeeid field and replace OrderTime with the name of the TreatmentTime field.


I was assuming that StartTime was a combination of date and time so an appointment that started at 9:30 on 4th February 2013 would have a value of 04/02/2013 09:30:00. If this is not correct please can you clarify. For example - do you actually have an appointment date field (called StartDate?) with a value of 04/02/2013 and a separate appointment time field (called StartTime?) with a value of 09:30:00. If so these can be combined by adding them together to create a single StartDateTime value

With regards the new record

What is needed is the startdate/time for the new record which you have either entered on the form or are prompted to enter during the update and the duration (TreatmentTime) and employee which is presumable entered on the form. It may be this is already provided when you doubleclick on the 'blank' area

i have a diary view that you dbl click to make a new appointment. each time 9:00, 9;15 etc is a text box and where an appointment already exists the dbl click will take you to a form where you can only view the existing appointment. where there is a blank space. ie no appointment it takes you to a form where you can then begin to build the appointment.

I thought the startdate/Time could be defined from the following which I got from your code

im not sure what the running total is. im not sure what it even does

I got it from your code DoCmd.RunSQL ("INSERT INTO tblStartTimeUpdate (OrdersItemsID, StartTime )SELECT RunningSumTotal.OrdersItemsID, [RunningTotal]-[TreatmentTime] AS StartTime FROM RunningSumTotal")
but just realised it is coming from another table so may not be relevant - perhaps you can work it out?

Hopefully the above gives you more to have a go at. I'm at a clients until the end of this week so will only be able to respond in the evenings for the next few days.

 

[shutzy]

i thought it would be best if i uploaded the relevant forms etc.

to get to the correct form open frmClientMainScreen
click Diary btn
either dbl click an appointment that is already there or a blank space.
if clicked blank space then at the bottom select a name and click 'Choose Treatments' btn

this will take you to the item builder form. this is where i need to be able to check for overlap. the code i pasted is in here.

hopefully we are now on the same page as far as my structure is.

let me know if you have any problems.

its in .mdb because it seems pretty standard for these forums.

thanks

 

[CJ_London]

Hi Shutzy,

Sorry for the late reply - I can see how to fix this, pretty much as I was suggesting - I'll be able to do it over the weekend since it does require a small modification to the form and it's late now and I'm at a client tomorrow.

However I can also see a better solution which I can easily implement if you can answer the following:

1. Lets say you select an inital appointment time of 1pm for customer Carole and employee Karen who is free until 2pm. You then select a 30 minute treatment (that is OK) but the next treatment is 45 minutes which won't fit in the remaining half hour with Karen. What do you do? look for a later space of 1hr 15 mins with Karen, split the appointment so Carole returns later to see Karen or look to see if Marie is free at 1:30 or something else?

2. Are your employees equal in terms ability to undertake any treatment - it looks like that is the case from an employee selection point of view which is limited only by their attendance hours at the initially selected appointment time?

3. Do you know, before you start selecting the treatments, roughly what the client requires and how long it will take so you look for a suitably large vacant period in the appointment diary?

What I'm thinking is to put a small display to the right of the screen showing the employee availability so you can effectively select a time for each treatment with the default of next treatment starting with the same employee immediately after the last treatment.

Thanks

 

[shutzy]

thanks for your suggestion. basically this is just a back up for employee error. most of the work is done by the most powerful computer(us). when a client phones up they generally let us know what they want or their needs and we calculate the time in our heads for the treatments. we then ask them what day/time they would prefer and give them some options. i want the check their for scenarios like.

a half leg and bikini wax are 30 mins each seperatley. if they are booked in at the same time it only takes 45 mins. so there is room for appointment times to be chopped down which is why i have added a time combo box. if a user thinks they can fit the items in in the chosen time frame but they forget to chop an items time then this is when i expect the overlap code to come into play.

the other scenario i have in mind is simple human error. calculating the items total time wrong. so for me its just a backup to stop bad data entry.

as far as the employees abilities go, none of them are equal. im not sure if i included tblEmployeeItems. this lists all the items that an employee can do and in the form, when you change the comboEmployee to any of them it should requery the lst box to show only the items that are in the tblEmployeeItems. so an item cannot be booked in with marie that is not in that tbl.

i do like the neat trick you suggested but im not sure if that is compicating things at this stage. i dont want the user to get confused when they think they have already got the appointment straight in their heads and then the database is telling them something else. i would rather a msg box saying there is not enough time available for this treatment.

just to note that what ever way we go with this i need to be able to do it to 2 other forms. im not altogether happy with adding anymore txt boxes etc to the form. in the record set thats ok. the reason is that i want the same code to work in frmAppointment-Status1And2 and frmAppointment-Status1And2-amendTreatments.

in those forms you will see the same code. but not neccessarily the same items within the form. so what i am saying is that wether it be by queries or DLookups that is the way i want to go.

thanks for the time on this.

 

[CJ_London]

Hi Shutzy,

This is now working - I have saved your original form with the same name and suffixed with -orig so if you want to revert simply rename the form.

There is a new module called PBModule which contains the function and there is one change to the form code.

In the code there is one call to the new function which I have indicated with a 'new code' comment

To deploy to the other forms, all you need to do is copy this line (starts If ApptClash....) to the equivalent place and change the names of the fields as required - I haven't checked but probably only the reference to other forms in this part of the line [Forms]![frmNewAppointment]![DateOfTreatment] + [Forms]![frmNewAppointment]![OrderTime] need to be changed

There are some basic assumptions:

• The staff hours are ignored so booking something after the employee has gone home will not be picked up as a problem
• for the purposestreatments are assumed to be in the order selected so the latest selection is at the end - i.e. you can't insert a treatment before another -but you need to test it out to double check
• I have provided an override to allow the appointment to go through as 'double booked' if required but you can comment this out and uncomment the two lines above to remove this feature

Anyway, see how you get on

Good luck!

 

[shutzy]

hi cj, oooh nearly. the problem i have come accross is that it seems to be chrcking against the tblOrders.OrderTime rather than tblOrdersItems.StartTime. the reason i think this is that when i first go to add a new appointment iit is working but it works with its own time. ie. i tried to book something in at 9am this morning. so i dbl clicked the diary.. clicked the client and went on to choose the treatments. when i tried to add the first treatment it comes up with the msg box stating a clash with an appointment at 9am.

im sure that this is an easy fix. but, you know the other forms i wanted to add this to. i have thought of a majr issue.

frmAppointmentStatus1And2 it is possible to change the date and time of the OrderID thus changing the start times of all the items in that order. the issue i now face is i need the function to loop through the items to check if there is time available. the only reference there is on that form is OrderID.

so it would be on click of btnSave the function with the loop and if all ok then run the update query etc. this is far out of my knowledge.

did you get my private message?

 

[shutzy]

hi cj. ive just been looking at the function and i think it needs to include in the sql statement that OrdersItemsID < OrdersItemsID.

ive tried but i am getting errors.

this is i think a simple fix as no new item added will ever be < any other as it will always be the newest.

thanks

 

[CJ_London]

Hi Shutzy,

I'll double check, the function is supposed to ignore orders with the same OrderID.

CJ

 

[CJ_London]

Hi Shutzy,

Ref this part:

the problem i have come accross is that it seems to be chrcking against the tblOrders.OrderTime rather than tblOrdersItems.StartTime. the reason i think this is that when i first go to add a new appointment iit is working but it works with its own time. ie. i tried to book something in at 9am this morning. so i dbl clicked the diary.. clicked the client and went on to choose the treatments. when i tried to add the first treatment it comes up with the msg box stating a clash with an appointment at 9am.

I haven't been able to replicate this problem but have checked the logic. The function has passed to it the start time of the new appointment (which is the sum of orderdate and ordertime from frmNewAppointment), the total order duration (sum of all the treatment times for the order in tblOrdersItems plus the new treatment time about to be added) and the employee.

The function then compares this with the calculated start and end times in tblOrdersItems. I can't see anything wrong with this logic (although there are some missing start times in tblOrdersItems which would produce unexpected results).

However, I've modified the code that calls the function to work off each treatment - see if this solves the problem.

frmAppointmentStatus1And2 it is possible to change the date and time of the OrderID thus changing the start times of all the items in that order. the issue i now face is i need the function to loop through the items to check if there is time available. the only reference there is on that form is OrderID.

Think this is where my suggested enhancement would have come in handy. It can be done - but a quicker solution may be to add another field called Duration with a Dsum control source - see attached db. Knowing this information you can move the forms around on the screen to expose the appointment diary to find a suitable slot and update start date and time accordingly. - As before, I've saved the original form with a '-orig' suffix if you don't want to use this change.

I've sent another copy of the db to your email with the changes. Please confirm you have received it

Thanks

CJ

 

[shutzy]

sorry cj. for some reason it didnt post my most recent post. i cant remember what i said now. i didnt receive your email. just to check the email address. gshutzy@msn.com. also that means i dont have the updated version. but after reading your post i dont want anymore txt boxes on the form, surely the sql can ignore the current record. if not then the sql can be like OrdersItemsID < [Forms]![frmAppointmentTreatmentItems]![OrdersItemsID]. i would normally do it myself but i cant get to grips with the sql. i usually copy and paste from the query design so when i need to make changes i just copy and paste back in then back out again.

thanks