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