Well, since it is easy to tell if a collision occurs, then just re-use the
same code to test for a collision in the forms before update event.
If there is a collisions, then simply do not allow the user to add the
record. You an put the code in the forms before update event. It really does
not matter what the other form display or not, the real issue here is you do
NOT simply allow a collision to occur.
It is not clear at what point you make the booking. Also, it is not clear
what that "display that "results"..and on closing is all about??? It is just
display, then re-code, or make a "general" routine, and use the same code in
the forms before update event. Since that "display" does not do
anything..then why worry about it? Let the user close the form..and
continue. Simply put in the forms (or sub-forms) before update event a test
to check for a closing, and do NOT allow the collision. I see nothing in the
"display" form that will effect the "test" for a collision in the forms
before update event to PREVENT the adding of the record...
If there is a collision, then give a user a message in the in the forms
before update event, and set cancel = true. The update will thus not be able
to occur.
The query to find a collision is very easy.
A collision (booking conflict) 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 (or if want, you don't even need a buttion, since when the
user exits, or tries to move to another reocrd,
, then the rocrd will try to be saved. So, you just need
a routine that runs the above sql, and tests for a collson.
You put this code in the forms before update event. If collsion
based on the above occurs, then set cancel = true, and the
form will not do the update.
You could also consider using a few un-bound controls here, and
add the booking via code. So, in place of allow ms-access to
auto save the reocrd, you could have a "book" buttion,
button, and then add the booking via code.
The "air" 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...
