Booking a camera - or not

[P Mitchell]

I want to enter a booking from 10am to 2pm, for say a camera (DC1), but the
camera may have already been booked from 9am to 3pm. I can create a query
that establishes the camera is already booked at 9am and who to and display
that result. But on closing and returning to the booking form whilst I know
how to code undo the record (booking) - but I don't know how to establish
there was a record shown in the query or whether there wasn't - and hence
whether to undo the booking or not.
Help/ideas appreciated.

Peter

 

[Albert D. Kallal]

The query to find a collision is very easy.

A collsion (booking conflicht) occurs when:


dateRequestStart <= EndDateandTime
and
dateRequresEnd >= StartDateandTime

The above query simply returns any collision. I suppose we would also add
the itemID that we are trying to book also.

dateRequestStart <= EndDateandTime
and
dateRequresEnd >= StartDateandTime
and
RequestItemID = ItemID

So, the above will tell us if the time/date we are trying to book has a
collision.

So, I would simply make booking form where you get the user to enter the
startdatetime, the enddatetime, and then select the item. You then hit the
book button. Behind that button you simply run the above query. Note that we
are talking about a few un-bound cotnorls here (however, you can/could just
use some contorls on the current reocrd). Anway, if there is NO collsion, we
then use code to add the new reocrd to the table (or permist the current
reocrd to be added, if you don't want a "book" buttion).


The code could/would look somthing like:

dim strSql as string
dim rstBooking as dao.recordset

strSql = "above sql example I gave"

set rstBooking = strSql

if rstBooking.recordCount > 0 then
msgbox "sorry...that itme is alrady booked"
goto MyExit:
end if

' make the booking....
rstBooking.AddNew
rstBooking!StartDateandTime = DateRequestStart
....etc

rstBooking.Update

myExit:
rstBooking.Close
set rstBooking = nothing.


As you can see it takes VERY little code here to handle this problem...