tricky validation code

[wjn london]

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:30M
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

 

[Jeff Boyce]

It sounds like you are trying to re-create some of the functionality that a
calendaring/scheduling application like, say, Microsoft Outlook already
provides.

Is there a reason this has to be done using Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dominic Vella]

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

 

[Peter Hibbs]

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/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4

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

 

[wjn london]

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

 

[Paul Shapiro]

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 where:
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.

 

[Paul Shapiro]

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);"

 

[wjn london]

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

 

[guzarva16]

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