Hi,
Please help, as soon i wont have much hair left!!!
I have constructed a db, that has vehicles, customers, order_details and driver tables.
I need when someone adds a new order to a form "order Details" that when they select "save" or close the current form event it searches in the relevent tables to check that the Vehicle and driver are not already booked. is not being used already.
My feilds on orders details form are
"Driver_name"
"Time_of_collection"
"time of delivery"
"date_of_booking"
"Vehicle ID".
Date format is DD/MM/YYYY (short date)
Time format is HH:MM
I am using Access 2007.
I have managed to write some code, but it saves it as normal but does not check if the vehicle or driver is in use for that date and time slot. Can you Help???
Private Sub Form_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
If ((Vehicle_ID = Me.Vehicle_ID) _
And (Me.Date_of_Booking = Me.Date_of_Booking.OldValue) _
And (Me.Time_of_Collection = Me.Time_of_Collection.OldValue) _
And (Me.Time_Of_delivery = Me.Time_Of_delivery.OldValue)) _
Or IsNull(Me.Vehicle_ID) _
Or IsNull(Me.Date_of_Booking) _
Or IsNull(Me.Time_of_Collection) _
Or IsNull(Me.Time_Of_delivery) Then
Else
strWhere = "(Vehicle_ID) = Me.Vehicle_ID & " And "(Date_of_booking" < " & (Me.date_of_booking) & " _
And (" &(Me.Time_of_Collection) & " < Time_of_Collection)
varResult = DLookup(Vehicle_ID, "Vehicles", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with booking " & varResult & "."
End If
End If
End Sub
Douglas J. Steele wrote:
You've got a lot of conditions in that If statement.
18-Nov-07
You've got a lot of conditions in that If statement. Are they all on the
same line (and it's wrapped in your post), or are they really on separate
lines? They need to be all on one line, or else you need to use continuation
characters
If ((Me.VehicleID = Me.VehicleID.OldValue)
And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue)
And(Me.BookingReturnDateAndTime =
Me.BookingReturnDateAndTime.OldValue))
Or IsNull(Me.VehicleID)
Or IsNull (Me.BookingOutDateAndTime)
Or IsNull (Me.BookingReturnDateAndTime)The
As well, regardless of your regional settings, you cannot use dd/mm/yyyy in
SQL statements (and even if you could, your strcJetDate is still incorrect).
Change it t
Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#
o
Const strcJetDate = "\#yyyy\-mm\-dd hh\:mm\:ss\#
If you were hoping to only compare down to the minute, ignoring seconds, you
can't do it in that way. You'd have to us
strWhere = "(VehicleID = " & Me.VehicleID & ")AN
(Format(BookingOutDateAndTime, "yyyymmddhhnn") < " &
Format(Me.BookingReturnDateAndTime, "yyyymmddhhnn"
& ") AND (" & Format(Me.BookingOutDateAndTime, "yyyymmddhhnn") & "
Format(BookingOutDateAndTime, "yyyymmddhhnn") AND (BookingID <> " &
Me.BookingID & ")
--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(no private e-mails, please
Previous Posts In This Thread:
Running a query to identify clashes
H
I am creating a Vehicle Bookings database and have created a query which
identifies any clashes for bookings of vehicles to eliminate double bookings
When a user books a car, i would like the database to check with the query
to see if the vehicle is available and if it is, accept the booking. If it is
already booked, i would like a message to appear telling the user to try
another time or another car
COuld someone please help me with this please
I have nearly finished it and this is one of the few final problems i have
Many thanks
Use the BeforeUpate event procedure of the Form where the entry is made.
Use the BeforeUpate event procedure of the Form where the entry is made. Use
DLookup() to see if there is a clashing entry
For help with DLookup(), see:
http://allenbrowne.com/casu-07.html
The basic idea is that there is a clash if:
- this booking starts before the other one ends, and
- the other one starts before this one ends, and
- it's the same vehicle, and
- it's not clashing with itself.
So it will be something like this aircode:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If ((Me.VehicleID = Me.VehicleID.OldValue) _
And (Me.StartDate = Me.StartDate.OldValue) _
And (Me.EndDate = Me.EndDate.OldValue)) _
Or IsNull(Me.VehicleID) _
Or IsNull(Me.StartDate) _
Or IsNull(Me.EndDate) Then
'do nothing
Else
strWhere = "(VehicleID = " & Me.VehicleID & _
") AND (StartDate < " & Format(Me.EndDate, strcJetDate) & _
") AND (" & Format(Me.StartDate, strcJetDate) & _
" < EndDate) AND (ID <> " & Me.ID & ")"
varResult = DLookup("ID", "BookingTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with booking " & varResult & "."
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Many thanks Allen.It was your query I used to establish clashing appointments!
Many thanks Allen.
It was your query I used to establish clashing appointments!
Do i need the query at all then?
thanks
:
Re: Running a query to identify clashes
If you are referrring to the query in:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html
that query checks every record in the table against every other record to
identify any clashes.
If you are only interested in the record that's about to be saved, you only
need to compare that one against all the others, so the Cartesian query is
not needed.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Thanks Allen.
Thanks Allen.
I have tried the code but i am getting several errors such as "syntax
error", "Expected: line number 0r label or statement or end of statement".
I have copied my code below:
Any more help would be really appreciated!
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#dd\/mm\/yyyy\/hh\/mm\#"
If ((Me.VehicleID = Me.VehicleID.OldValue)
And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue)
And(Me.BookingReturnDateAndTime =
Me.BookingReturnDateAndTime.OldValue))
Or IsNull(Me.VehicleID)
Or IsNull (Me.BookingOutDateAndTime)
Or IsNull (Me.BookingReturnDateAndTime)Then
'do nothing
Else
strWhere = "(VehicleID = " & Me.VehicleID & ")AND
(BookingOutDateAndTime < " & Format(Me.BookingReturnDateAndTime, strcJetDate)
& ") AND (" & Format(Me.BookingOutDateAndTime, strcJetDate) & " <
BookingOutDateAndTime) AND (BookingID <> " & Me.BookingID & ")"
varResult = DLookup("BookingID", "tblBookings", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with booking " & varResult & "."
End If
End If
End Sub
:
You've got a lot of conditions in that If statement.
You've got a lot of conditions in that If statement. Are they all on the
same line (and it's wrapped in your post), or are they really on separate
lines? They need to be all on one line, or else you need to use continuation
characters:
If ((Me.VehicleID = Me.VehicleID.OldValue) _
And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue) _
And(Me.BookingReturnDateAndTime = _
Me.BookingReturnDateAndTime.OldValue)) _
Or IsNull(Me.VehicleID) _
Or IsNull (Me.BookingOutDateAndTime) _
Or IsNull (Me.BookingReturnDateAndTime)Then
As well, regardless of your regional settings, you cannot use dd/mm/yyyy in
SQL statements (and even if you could, your strcJetDate is still incorrect).
Change it to
Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#"
or
Const strcJetDate = "\#yyyy\-mm\-dd hh\:mm\:ss\#"
If you were hoping to only compare down to the minute, ignoring seconds, you
can't do it in that way. You'd have to use
strWhere = "(VehicleID = " & Me.VehicleID & ")AND
(Format(BookingOutDateAndTime, "yyyymmddhhnn") < " &
Format(Me.BookingReturnDateAndTime, "yyyymmddhhnn")
& ") AND (" & Format(Me.BookingOutDateAndTime, "yyyymmddhhnn") & " <
Format(BookingOutDateAndTime, "yyyymmddhhnn") AND (BookingID <> " &
Me.BookingID & ")"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Thanks for your help.
Thanks for your help.
Whenever i make a booking which clashes it accepts it regardless!
My code is below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#"
If ((Me.VehicleID = Me.VehicleID.OldValue) And (Me.BookingOutDateAndTime
= Me.BookingOutDateAndTime.OldValue) And (Me.BookingReturnDateAndTime =
Me.BookingReturnDateAndTime.OldValue)) Or IsNull(Me.VehicleID) Or
IsNull(Me.BookingOutDateAndTime) Or IsNull(Me.BookingReturnDateAndTime) Then
'do nothing
Else
strWhere = "(VehicleID = " & Me.VehicleID & ")AND
(BookingOutDateAndTime < " & Format(Me.BookingReturnDateAndTime, strcJetDate)
& ") AND (" & Format(Me.BookingOutDateAndTime, strcJetDate) & " <
BookingOutDateAndTime) AND (BookingID <> " & Me.BookingID & ")"
varResult = DLookup("BookingID", "tblBookings", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with booking " & varResult & "."
End If
End If
End Sub
:
What's actually being written to strWhere? Is it correct?
What's actually being written to strWhere? Is it correct?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
To be honest, I really don't know what is going on!
To be honest, I really don't know what is going on!
Can you please help me get this right as i is one of the few things i need
to do to complete the database.
All i want to do is check that there are no bookings already made for the
vehicle the user wishes to book.
many thanks
:
It seems there is an error in the formulation.
It seems there is an error in the formulation. The check on a colliding
interval must be like:
AnyExistingStart < NewProposedEnding AND AnyExistingEnd >
NewProposedStarting
the one you use is
BookingOut < NewProposedReturning AND NewProposedStarting <
BookingOut
ie, you use BookingOut twice !
Vanderghast, Access MVP
Checking for Overlapping Events in access 2007
Hi,
Please help, as soon i wont have much hair left!!!
I have constructed a db, that has vehicles, customers, order_details and driver tables.
I need when someone adds a new order to a form "order Details" that when they select "save" or close the current form event it searches in the relevent tables to check that the Vehicle and driver are not already booked. is not being used already.
My feilds on orders details form are
"Driver_name"
"Time_of_collection"
"time of delivery"
"date_of_booking"
"Vehicle ID".
Date format is DD/MM/YYYY (short date)
Time format is HH:MM
I am using Access 2007.
I have managed to write some code, but it saves it as normal but does not check if the vehicle or driver is in use for that date and time slot. Can you Help???
Private Sub Form_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
If ((Vehicle_ID = Me.Vehicle_ID) _
And (Me.Date_of_Booking = Me.Date_of_Booking.OldValue) _
And (Me.Time_of_Collection = Me.Time_of_Collection.OldValue) _
And (Me.Time_Of_delivery = Me.Time_Of_delivery.OldValue)) _
Or IsNull(Me.Vehicle_ID) _
Or IsNull(Me.Date_of_Booking) _
Or IsNull(Me.Time_of_Collection) _
Or IsNull(Me.Time_Of_delivery) Then
Else
strWhere = "(Vehicle_ID) = Me.Vehicle_ID & " And "(Date_of_booking" < " & (Me.date_of_booking) & " _
And (" &(Me.Time_of_Collection) & " < Time_of_Collection)
varResult = DLookup(Vehicle_ID, "Vehicles", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with booking " & varResult & "."
End If
End If
End Sub
Submitted via EggHeadCafe - Software Developer Portal of Choice
File Management For No-Touch Deployments
http://www.eggheadcafe.com/tutorial...ef-ec39c7640d46/file-management-for-noto.aspx