If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
tricky validation code
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. Thanks for any help in advance Warren |
#3
|
|||
|
|||
tricky validation code
Yes that is tricky
Well, whilst I can think of some technical ways of doing this, it may be best to add a "Check Availability" button. In there I would write a routine to do the check. Without knowing your field and table names it would be difficult to explain any suggested code. Dom. www.effectivedata.com.au "wjn london" wjn wrote in message ... I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. Thanks for any help in advance Warren |
#4
|
|||
|
|||
tricky validation code
Warren,
This problem is tricky but I do it like this. You haven't given us much information so I will make a few assumptions. I will assume you have a table which holds the existing bookings (call it tblDiary) and it has an AutoNumber field called ID, a Date/Time field called StartTime which holds the time AND date of the start time for a booking and also another field called EndTime which holds the time AND date of the end time of the booking. I'll also assume you are only concerned with bookings that do not run into two or more days. The code below will handle that with a small modification. The trick is to compare the Start and End times of the new booking with the start and end times of each existing booking, if the new start time is less than the existing booking end time AND the new end time is greater than the existing booking start time then the time periods must overlap. It takes a bit of lateral thinking to get your head round the logic but it works (I hope). To try it out paste the code below into your form code module and change the table and field names as appropriate. Call it with the new start and end time like this :- Dim vID As Long vID = ApptCheck(txtStartTime, txtEndTime) If vID 0 then MsgBox "Times overlap" Exit Sub End If 'Add new record to table here. If the time periods overlap the function returns the ID of the booking record which it overlaps, you can then use this information to show the user which existing booking record already exists for that time. The way I normally do it is to create an unbound form which has controls such as Text and Combo boxes to enter the booking information. I have an 'OK' button which, when the user has entered the required start and end times and any other info, he clicks which does the above test and then adds a new record to the table or shows an appropriate error message. You could also do it the way you mentioned using the Before Update event but I don't like adding records to tables and then removing them, especially in a multi-user system. Another option you might like to consider is to show the existing bookings in a 'graphical mode' so that the user can see all the existing bookings along with the start and end times. This will make it a lot easier for them to see when they are trying to create an overlapping booking. You can do this with a Flex Grid control, see this site for my Flex Grid demo program which does just that :- http://www.rogersaccesslibrary.com/f...3b 1d2zcccfb4 I also have another Flex Grid demo which does more or less what you are trying to do, if you are interested in seeing it then drop me an email, my address is in the Flex Grid documentation. Public Function ApptCheck(vStart As Date, vEnd As Date) As Long 'Checks if appointment overlaps existing appointment 'Entry vStart = Start date and time of new appt ' vEnd = End date and time of new appt 'Exit ApptCheck = ID of existing app't if new appt overlaps or 0 if no overlap Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblDiary WHERE " _ & "DateValue(StartTime) = #" & Format(vStart, "yyyy/m/d") & "#") Do Until rst.EOF If vStart rst!EndTime And vEnd rst!StartTime Then ApptCheck = rst!ID End If rst.MoveNext Loop rst.Close Set rst = Nothing End Function (Watch out for word wrapping when you paste the code). HTH Peter Hibbs. On Tue, 23 Sep 2008 13:52:01 -0700, wjn london wjn wrote: I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. Thanks for any help in advance Warren |
#5
|
|||
|
|||
tricky validation code
Peter,
Thanks for the great reply you've given me lots to think about. You're assumptions where almost correct (in fact i store the date in a seperate field from the start and end times). I'm sure I will be able to devise a solution from here, and I will definately take a look at the flex grid over the weekend when i've got some time. Thanks again Warren "Peter Hibbs" wrote: Warren, This problem is tricky but I do it like this. You haven't given us much information so I will make a few assumptions. I will assume you have a table which holds the existing bookings (call it tblDiary) and it has an AutoNumber field called ID, a Date/Time field called StartTime which holds the time AND date of the start time for a booking and also another field called EndTime which holds the time AND date of the end time of the booking. I'll also assume you are only concerned with bookings that do not run into two or more days. The code below will handle that with a small modification. The trick is to compare the Start and End times of the new booking with the start and end times of each existing booking, if the new start time is less than the existing booking end time AND the new end time is greater than the existing booking start time then the time periods must overlap. It takes a bit of lateral thinking to get your head round the logic but it works (I hope). To try it out paste the code below into your form code module and change the table and field names as appropriate. Call it with the new start and end time like this :- Dim vID As Long vID = ApptCheck(txtStartTime, txtEndTime) If vID 0 then MsgBox "Times overlap" Exit Sub End If 'Add new record to table here. If the time periods overlap the function returns the ID of the booking record which it overlaps, you can then use this information to show the user which existing booking record already exists for that time. The way I normally do it is to create an unbound form which has controls such as Text and Combo boxes to enter the booking information. I have an 'OK' button which, when the user has entered the required start and end times and any other info, he clicks which does the above test and then adds a new record to the table or shows an appropriate error message. You could also do it the way you mentioned using the Before Update event but I don't like adding records to tables and then removing them, especially in a multi-user system. Another option you might like to consider is to show the existing bookings in a 'graphical mode' so that the user can see all the existing bookings along with the start and end times. This will make it a lot easier for them to see when they are trying to create an overlapping booking. You can do this with a Flex Grid control, see this site for my Flex Grid demo program which does just that :- http://www.rogersaccesslibrary.com/f...3b 1d2zcccfb4 I also have another Flex Grid demo which does more or less what you are trying to do, if you are interested in seeing it then drop me an email, my address is in the Flex Grid documentation. Public Function ApptCheck(vStart As Date, vEnd As Date) As Long 'Checks if appointment overlaps existing appointment 'Entry vStart = Start date and time of new appt ' vEnd = End date and time of new appt 'Exit ApptCheck = ID of existing app't if new appt overlaps or 0 if no overlap Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblDiary WHERE " _ & "DateValue(StartTime) = #" & Format(vStart, "yyyy/m/d") & "#") Do Until rst.EOF If vStart rst!EndTime And vEnd rst!StartTime Then ApptCheck = rst!ID End If rst.MoveNext Loop rst.Close Set rst = Nothing End Function (Watch out for word wrapping when you paste the code). HTH Peter Hibbs. On Tue, 23 Sep 2008 13:52:01 -0700, wjn london wjn wrote: I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. Thanks for any help in advance Warren |
#6
|
|||
|
|||
tricky validation code
You can check in the BeforeUpdate routine for the new row. If you want to
identify the existing conflicting booking, you could use something like this: strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where " _ & strProposedStart & " Between startDateTime And EndDateTime " _ & "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _ & "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & " EndDateTime);" The logic identifies a conflicting row as one whe a) the new start is within an existing booking or b) the new end is within an existing booking or c) the new booking "surrounds" an existing booking Opening a recordset with this expression returns any overlapping existing row(s), which you can choose to display to the user and then cancel the update. If the recordset is empty, there's no conflict. If you only want to know if there is a conflict, and don't need the details, use a Count function and check for greater than zero. "Dominic Vella" wrote in message ... Yes that is tricky Well, whilst I can think of some technical ways of doing this, it may be best to add a "Check Availability" button. In there I would write a routine to do the check. Without knowing your field and table names it would be difficult to explain any suggested code. Dom. www.effectivedata.com.au "wjn london" wjn wrote in message ... I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. Thanks for any help in advance Warren |
#7
|
|||
|
|||
tricky validation code
Actually, I think the logic can be simplified a bit. To avoid a conflict,
the new start time must be greater than the existing end time, or the new end time must be before the existing start time. So a conflict is the netative of this. strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where NOT (" _ & strProposedStart & " endDateTime Or & strProposedEnd startDateTime);" "Paul Shapiro" wrote in message ... You can check in the BeforeUpdate routine for the new row. If you want to identify the existing conflicting booking, you could use something like this: strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where " _ & strProposedStart & " Between startDateTime And EndDateTime " _ & "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _ & "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & " EndDateTime);" The logic identifies a conflicting row as one whe a) the new start is within an existing booking or b) the new end is within an existing booking or c) the new booking "surrounds" an existing booking Opening a recordset with this expression returns any overlapping existing row(s), which you can choose to display to the user and then cancel the update. If the recordset is empty, there's no conflict. If you only want to know if there is a conflict, and don't need the details, use a Count function and check for greater than zero. "Dominic Vella" wrote in message ... Yes that is tricky Well, whilst I can think of some technical ways of doing this, it may be best to add a "Check Availability" button. In there I would write a routine to do the check. Without knowing your field and table names it would be difficult to explain any suggested code. Dom. www.effectivedata.com.au "wjn london" wjn wrote in message ... I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. |
#8
|
|||
|
|||
tricky validation code
Paul,
Thanks a lot for the help. Between you and peter I now have lots of options and will spend some time experimenting with the code. Of course there may be more than 1 booking per day of varying lengths but i get the idea of using the select statement to scan the bookings table and the logic to check for clashes. thanks again warren "Paul Shapiro" wrote: Actually, I think the logic can be simplified a bit. To avoid a conflict, the new start time must be greater than the existing end time, or the new end time must be before the existing start time. So a conflict is the netative of this. strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where NOT (" _ & strProposedStart & " endDateTime Or & strProposedEnd startDateTime);" "Paul Shapiro" wrote in message ... You can check in the BeforeUpdate routine for the new row. If you want to identify the existing conflicting booking, you could use something like this: strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where " _ & strProposedStart & " Between startDateTime And EndDateTime " _ & "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _ & "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & " EndDateTime);" The logic identifies a conflicting row as one whe a) the new start is within an existing booking or b) the new end is within an existing booking or c) the new booking "surrounds" an existing booking Opening a recordset with this expression returns any overlapping existing row(s), which you can choose to display to the user and then cancel the update. If the recordset is empty, there's no conflict. If you only want to know if there is a conflict, and don't need the details, use a Count function and check for greater than zero. "Dominic Vella" wrote in message ... Yes that is tricky Well, whilst I can think of some technical ways of doing this, it may be best to add a "Check Availability" button. In there I would write a routine to do the check. Without knowing your field and table names it would be difficult to explain any suggested code. Dom. www.effectivedata.com.au "wjn london" wjn wrote in message ... I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. |
#9
|
|||
|
|||
tricky validation code
"wjn london" wrote in message ... Paul, Thanks a lot for the help. Between you and peter I now have lots of options and will spend some time experimenting with the code. Of course there may be more than 1 booking per day of varying lengths but i get the idea of using the select statement to scan the bookings table and the logic to check for clashes. thanks again warren "Paul Shapiro" wrote: Actually, I think the logic can be simplified a bit. To avoid a conflict, the new start time must be greater than the existing end time, or the new end time must be before the existing start time. So a conflict is the netative of this. strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where NOT (" _ & strProposedStart & " endDateTime Or & strProposedEnd startDateTime);" "Paul Shapiro" wrote in message ... You can check in the BeforeUpdate routine for the new row. If you want to identify the existing conflicting booking, you could use something like this: strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy hh:nn") & "# " strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") & "# " strSQL = "Select * From tblBooking Where " _ & strProposedStart & " Between startDateTime And EndDateTime " _ & "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _ & "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & " EndDateTime);" The logic identifies a conflicting row as one whe a) the new start is within an existing booking or b) the new end is within an existing booking or c) the new booking "surrounds" an existing booking Opening a recordset with this expression returns any overlapping existing row(s), which you can choose to display to the user and then cancel the update. If the recordset is empty, there's no conflict. If you only want to know if there is a conflict, and don't need the details, use a Count function and check for greater than zero. "Dominic Vella" wrote in message ... Yes that is tricky Well, whilst I can think of some technical ways of doing this, it may be best to add a "Check Availability" button. In there I would write a routine to do the check. Without knowing your field and table names it would be difficult to explain any suggested code. Dom. www.effectivedata.com.au "wjn london" wjn wrote in message ... I am developing a db for my local community centre. They hire out some of thier facilities. In the booking table and form i have various validation expressions which limit start times and end times and they work fine. I use a secondary index on the bookings table to ensure that no 2 bookings can be booked on the same day, same start time and for the same resource. eg. Main hall on 2/2/08 at 9:30:AM my problem is How do I trap for overlapping bookings EG Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM this is missed because the start time is different but it still falls within the booking. Hope this is clear. I think i need a way to scan through the bookings on "before update" and somehow check if my new start time falls between any previously entered start-end times. then cancel update if needed or allow update if no conflict. |
Thread Tools | |
Display Modes | |
|
|