Booking duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
I am putting the finishing touches on a project. I have received a lot of
help from everyone and do appreciate it very much. This question concerns
preventing duplicate bookings on equipment. The user has a large inventory of
equipment (about 100 pcs.), that he books for certain days and times. He
needs to be prompted if he is about to overbook based on date and time. Also
I would like go to the conflicting order when the prompt is answered. Can
someone help with code, or point me in the right direction? I am not up to
speed on programming.
Thanks
 
Two events overlap if:
A starts before B ends, AND
B starts before A ends, AND
A and B are not the same event.

To check for this when a record is being entered, use the BeforeUpdate event
of the Form, and use DLookup to get the EventID (or whatever your primary
key is called) for the first event that clashes.

This aircode example assumes a table named tblEvent, with a primary key
autonumber named EventID, and date/time fields named Start and End which are
both required:

Private Sub Form_BeforeUdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy hh:nn:ss\#"

If Me.Start = Me.Start.OldValue OR Me.End = Me.End.OldValue Then
'do nothing
Else
If IsNull(Me.Start) OR IsNull(Me.End) Then
Cancel = True
MsgBox "Start and End dates required."
Else
strWhere = "(Start < " & Format(Me.End, strcJetDate) & _
") AND (" & Format(Me.Start, strcJetDate) & _
" < End) AND (EventID <> " & Me.EventID & ")"
varResult = DLookup("EventID", "tblEvent", strWhere)
If Not IsNull(varResult) Then
MsgBox "Event " & varResult & " clashes."
Cancel = True
Me.Undo
'Add your code here to move to the other record.
End If
End If
End If
End Sub

The code to move to the other record will involve a FindFirst in the
RecordsetClone of the form, similar to this one:
http://allenbrowne.com/ser-03.html

If you want to compare all events against all other events to find clashes,
or handle the possibility of open-ended events (end date/time unknown), see:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html
 
Hi Allen

Thanks for the response. Does the code you supplied indicate the Start Date
and Start Time are in the same field? My fields are separate. How would this
affect the code?
 
Put them in the same field. If you require just the date, or just the time,
you can use the DateValue or TimeValue.

If you cannot (or will not) make that correction to your database (it
actually is an error to have them as two separate fields...), simply add the
two values together to combine them.
 
Ok, so I'm learning. Does the user actually input the Date and Time into one
field, or is there another way that is done? An input mask would need to
created prompting for both.
 
You could have them put both in the same field, although it would probably
be easier to let them enter them separately. (Personally, I like to give
them a calendar, such as what Stephen Lebans has at
http://www.lebans.com/monthcalendar.htm, rather than making them key dates
in). Your date and time fields don't need to be bound, though: once they've
entered both, you can add them together and assign the value to the actual
DateTime field.
 
Thanks
I will take a look at the calender.> You could have them put both in the
same field, although it would probably
 
The code isn't working because of something I'm doing wrong. My Date and Time
fields are on my "Orders" Form. ("Orders" table/"OrderID" is primary) The
product I'm trying not to duplicate Date and Time on is on a sub form on the
"Orders" form. It is in the "Products" table and "ProductID" is the primary.
Using Allen's code, I substituted StartTime and EndTime for Start and End,
"OrderID" for "EventID", and "Orders" for "tblEvent".
Can you see what I'm doing wrong?
 
Do you get an error message?
What message?
Which line gives the error?
Does the code compile? (Compile on Debug menu)

It it compiles, and you cannot see the error with the line that generates
it, post your Form_BeforeUpdate as you have it.
 
Allen
There is no error message and the code compiles. Just so you understand: On
my "Orders" form you tab through the customer information, then the date
fields, then you go into a sub form that has the product in it, with a drop
down so you can select the product. Nothing happens now as I tab through the
complete order. How does this code tie the dates and times to the product in
the sub form? Here is the before update code as I have it:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy#"

If Me.StartDate = Me.StartDate.OldValue Or Me.EndDate =
Me.EndDate.OldValue Then 'do nothing

Else
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
Cancel = True
MsgBox "Start and End dates required."
Else
strWhere = "(StartDate < " & Format(Me.EndDate, strcJetDate) &
") AND (" & Format(Me.StartDate, strcJetDate) & _
" < EndDate) AND (OrderID <> " & Me.OrderID & ")"
varResult = DLookup("OrderID", "Orders", strWhere)
If Not IsNull(varResult) Then
MsgBox "Event " & varResult & " clashes."
Cancel = True
Me.Undo
'Add your code here to move to the other record.

End If
End If
End If
End Sub
Thanks Allen
 
Re-reading your thread, it appears you have a main table bound to the Orders
table, with a subform bound to the Products table. That makes no sense to
me.

I presume you are hiring items out, since the orders have a date range. If
so, one product will be hired out many times, and we also know that one
order can have many products. This means you have a many-to-many relation
between orders and products. You will resolve that with a third table that
specifies what was hired when.

I suspect that someone one day will need to hire products for different
periods, so I suggest the hire dates are best in the junction table. You
will therefore have tables like this:

Product table (one record for each product instance):
ProductID Autonumber primary key
...

Client table (one record for each hirer):
ClientID Autonumber primary key

Order table (one record for each order):
OrderID AutoNumber primary key
OrderDate Date/Time when the order was taken

OrderDetail table (one record for each product hired in an order):
OrderDetailID AutoNumber primary key
OrderID Number which order this row belongs to.
ProductID Number what product was hired.
StartDate Date/Time when this item was taken
DueDate Date/Time when this item is due back
ReturnDate Date/Time when this item was actually returned.

With this structure, the subform will be bound to the OrderDetail table (not
the Product table), and the code will go into the subform's
Form_BeforeUpdate event. The code will refer will compare the dates and the
ProductID (not the OrderID) looking for clashes.

You must use the junction table (OrderDetail) to resolve the many to many,
but you could keep the StartDate, DueDate, and ReturnDate in the Order table
if you were convinced that no-one would ever need to hire items for
different periods, and noone would ever fail to return every piece of the
order on time. If you want to do that, you will need to use both the
BeforeUpdate event of the main form and the BeforeUpdate event of the
subform to look for clashes. The logic will be more involved, as you will
have to check all the products in the order against the dates.
 
Allen
So I don't get too confused, this is what I have now:
Product table (one record for each product instance):
ProductID Autonumber primary key
Customer table (one record for each hirer):
CustomerID Autonumber primary key
Orders table (one record for each order):
OrderID AutoNumber primary key
OrderDate Date/Time when the order was taken
StartDate Date/Time when this item was taken
DueDate Date/Time when this item is due back
OrderDetail table (one record for each product hired in an order):
OrderDetailID AutoNumber primary key
OrderID Number which order this row belongs to.
ProductID Number what product was hired.

The only difference is where I have the StartDates and the DueDates. The
relationship between the "Products" Table and the "Orders" table is a one to
many which I confirmed looking at my relationships. They are connected from
ProductID on the "Products" table (the one side) to the ProductID on the
"OrderDetails" table(the many side).
"Customer" table (CustomerID) to "Orders" Table (CustomerID) one to many.
"Orders" table (OrderID) to "OrderDetails" table (OrderID) one to many.

Allen, I hope this makes some sense to you. I just want to be sure I should
follow your previous instruction.
 
Allen
The sub form is bound to "OrderDetails"

Allen Browne said:
Re-reading your thread, it appears you have a main table bound to the Orders
table, with a subform bound to the Products table. That makes no sense to
me.

I presume you are hiring items out, since the orders have a date range. If
so, one product will be hired out many times, and we also know that one
order can have many products. This means you have a many-to-many relation
between orders and products. You will resolve that with a third table that
specifies what was hired when.

I suspect that someone one day will need to hire products for different
periods, so I suggest the hire dates are best in the junction table. You
will therefore have tables like this:

Product table (one record for each product instance):
ProductID Autonumber primary key
...

Client table (one record for each hirer):
ClientID Autonumber primary key

Order table (one record for each order):
OrderID AutoNumber primary key
OrderDate Date/Time when the order was taken

OrderDetail table (one record for each product hired in an order):
OrderDetailID AutoNumber primary key
OrderID Number which order this row belongs to.
ProductID Number what product was hired.
StartDate Date/Time when this item was taken
DueDate Date/Time when this item is due back
ReturnDate Date/Time when this item was actually returned.

With this structure, the subform will be bound to the OrderDetail table (not
the Product table), and the code will go into the subform's
Form_BeforeUpdate event. The code will refer will compare the dates and the
ProductID (not the OrderID) looking for clashes.

You must use the junction table (OrderDetail) to resolve the many to many,
but you could keep the StartDate, DueDate, and ReturnDate in the Order table
if you were convinced that no-one would ever need to hire items for
different periods, and noone would ever fail to return every piece of the
order on time. If you want to do that, you will need to use both the
BeforeUpdate event of the main form and the BeforeUpdate event of the
subform to look for clashes. The logic will be more involved, as you will
have to check all the products in the order against the dates.
Click to expand...
 
If you want to keep StartDate and DueDate in the Orders table, then you will
need to run the suggested code in the BeforeUpdate event of the subform, and
DLookup() a query that contains both tables (Order and OrderDetail), based
on the dates and the ProductID to see if there is a clash. Since the dates
are in the main form, the criteria string (strWhere) will need to
concatenate the dates from the parent form, i.e.:
Me.Parent!StartDate

You will also need to write a more involved routine in the BeforeUpdate
event of the main form, to check for clashes on all the products in the
subform if the dates have changed. Otherwise an order could be changed to
extend the DueDate (for example) after the subform entries have been made,
and the subform's code would not have found the clashes that result from
extending the dates. I cannot continue writing that code for you.
 
Back
Top