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