Booking database

[Mike Glazer]

I am trying to construct an Access database to be used for making bookings
to share vehicles. The user has to be able to select a starting date and
time and an ending date and time. However, when the user tries to select a
period already booked the system needs to object and not allow it. Any ideas
how this can be achieved?

Mike Glazer

[Allen Browne]

Use the BeforeUpdate event of the form to run the check and see if this
vehicle is already booked.

You can do that with a DLookup() if you get the Criteria argument right.

The basic idea is that there is a clash between event A and event B if:
A starts before B ends, and
B starts before A ends, and
they are both for the same vehicle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Glazer" <gl****@physics.ox.ac.uk> wrote in message
news:d6**********@news.ox.ac.uk...

I am trying to construct an Access database to be used for making bookings
to share vehicles. The user has to be able to select a starting date and
time and an ending date and time. However, when the user tries to select a
period already booked the system needs to object and not allow it. Any
ideas how this can be achieved?

[ByteMyzer]

"Mike Glazer" <gl****@physics.ox.ac.uk> wrote in message news:d6**********@news.ox.ac.uk...

I am trying to construct an Access database to be used for making bookings
to share vehicles. The user has to be able to select a starting date and
time and an ending date and time. However, when the user tries to select a
period already booked the system needs to object and not allow it. Any ideas
how this can be achieved?

Mike Glazer

The following is a rather simplified example, which I'm sure will set you on the right track:

Create a table named v_Book with the following fields:
Book_ID (Autonumber, Incremental)
Vehicle_ID (Text)
Date_Time_Out (Date/Time)
Date_Time_In (Date/Time)
Cancelled (Yes/No)

Paste the following code into a new module:

--------------------------------------------------------------------------------

Public Function BookID(VehicleID As String, DateOut As Date, DateIn As Date) As Long

If DateOut >= DateIn Then
MsgBox "Date In must be greater than Date Out", vbExclamation
Exit Function
End If

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT v_Book.* " _
& "FROM v_Book " _
& "WHERE v_Book.Vehicle_ID='" & VehicleID & "' " _
& "AND v_Book.Cancelled=False " _
& "AND ((v_Book.Date_Time_Out<#" & DateOut & "# AND v_Book.Date_Time_In>#" & DateOut & "#) " _
& "OR (v_Book.Date_Time_Out<#" & DateIn & "# AND v_Book.Date_Time_In>#" & DateIn & "#)) " _
& ";")

If rs.RecordCount Then
MsgBox "Vehicle: " & VehicleID & vbNewLine _
& "is already booked" & vbNewLine _
& "from " & rs!Date_Time_Out & vbNewLine _
& "to " & rs!Date_Time_In
rs.Close
Exit Function
End If
rs.Close

Set rs = CurrentDb.OpenRecordset("v_Book", dbOpenDynaset)
rs.AddNew
rs!Vehicle_ID = VehicleID
rs!Date_Time_Out = DateOut
rs!Date_Time_In = DateIn
BookID = rs!Book_ID
rs.Update
rs.Close

Set rs = Nothing

End Function

--------------------------------------------------------------------------------

To test this code, try the following from the Debug window:
?BookID("CS10",#5/16/2005 6:00:00 PM#,#5/17/2005 12:00:00 PM#)
The result should be a Long Integer with the Booking ID.

Now try:
?BookID("CS10",#5/17/2005 11:59:00 AM#,#5/17/2005 12:00:00 PM#)
Because the DateOut is in between the DateOut and DateIn of an already existing booking which has not been cancelled, you should get a MessageBox stating:
Vehicle: CS10
is already booked
from 5/16/2005 6:00:00 PM
to 5/17/2005 12:00:00 PM

See if this method works for you.