473,693 Members | 2,871 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Booking database

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
Nov 13 '05 #1
2 2857
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.u k...
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?

Nov 13 '05 #2
"Mike Glazer" <gl****@physics .ox.ac.uk> wrote in message news:d6******** **@news.ox.ac.u k...
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(VehicleI D 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.OpenR ecordset("SELEC T v_Book.* " _
& "FROM v_Book " _
& "WHERE v_Book.Vehicle_ ID='" & VehicleID & "' " _
& "AND v_Book.Cancelle d=False " _
& "AND ((v_Book.Date_T ime_Out<#" & DateOut & "# AND v_Book.Date_Tim e_In>#" & DateOut & "#) " _
& "OR (v_Book.Date_Ti me_Out<#" & DateIn & "# AND v_Book.Date_Tim e_In>#" & DateIn & "#)) " _
& ";")

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

Set rs = CurrentDb.OpenR ecordset("v_Boo k", dbOpenDynaset)
rs.AddNew
rs!Vehicle_ID = VehicleID
rs!Date_Time_Ou t = 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.
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
16814
by: PaulD | last post by:
Can anyone point me in the direction of a sample Access booking system? Trying to build a booking system were customers can book a computer by date and timeslot, each timeslot having a limited amount of computers. Cheers for anyhelp, pointers given. PaulD
4
2442
by: Grant | last post by:
Hi I have a database which logs the usage of rooms. Some booking are entered well in advance, and some have stays of more than six months. I would like to ensure that rooms which have been booked cannot be booked again, i.e. cannot be booked in that date range Any help will be much appreciated
4
3249
by: WiseOwl | last post by:
Hi folks I teach. At school, four IT rooms are booked using a paper based outline timetable. Completing it is easy but basic and impossible to ensure completion of all fields (name, year group, subject and software) and analysis of bookings is a nightmare. I just fancied pottering with Access, which I am pretty familiar with, to see if I could create a means of booking a room using a database. Locking a booking would then be a...
2
8230
by: Andy | last post by:
Hi folks I teach. At school, four IT rooms are booked using a paper based outline timetable. Completing it is easy but basic and impossible to ensure completion of all fields (name, year group, subject and software) and analysis of bookings is a nightmare. I just fancied pottering with Access, which I am pretty familiar with, to see if I could create a means of booking a room using a database. Locking a booking would then be a...
2
4222
by: MarcusB | last post by:
Hi, At our place we have Video Projector which people use to book. So far we have on paper booking system. I need some help in writing asp (web application) which will help us in booking procedure. The application should look when projector is not booked display calendar or list free times. User will specufie the day (days) and time when she or he want borrow projector. Is there some ready scripts which I can adjust to our purpose. ...
25
2856
by: NDayave | last post by:
How do, I have a access 2000 booking database with Personal Details, Outing Details and the Bookings in three tables: tblBookings -- -Autonumber, -Number, -Number, -Currency, -Yes/No, -Yes/No tblPersonaldetails -- -Autonumber, etc tblOutingDetails -- -Autonumber, etc
5
2747
by: Armenante | last post by:
Hi, I am creating a database for my A level project and i am stuck on the validation for the booking part of the database, and was just wondering if anyone could help me out? It is a plumbing booking and stock database in the booking section I have start date, start time and duration field. I want to add validation on the start date field as an after update event. Any ideas would be extremely helpful. Thank you. Tom
3
3657
by: Wildster | last post by:
Hi, I have a form in Access 2000 running Windows XP with the following fields: - Booking_ID Date Person_Name Booking_Type 8-9am (y/n checkbox) 9-10am (y/n checkbox)
3
3067
by: 88stevie | last post by:
Hi all, I have a system and I'm a little stuck. It's a booking system. It has rooms, etc and every room have a daily price. Then it has special daily rates when assigned. How do I calculate the total cost of a room if a daily rate exists?
0
9100
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8970
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8815
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7643
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6475
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5824
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4332
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4565
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2245
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.