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