Re: Booking duplicates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 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.

"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4FA6F9B7-E96D-4678-82CD-52405ABA3A14@xxxxxxxxxxxxxxxx
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

"Allen Browne" wrote:

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 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.

"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9CBDC410-0031-4DE8-87E1-4A81EA488F29@xxxxxxxxxxxxxxxx
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?

"Ron Weaver" wrote:

Thanks
I will take a look at the calender.> 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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71E7A665-A729-4C08-980E-47432136CA2D@xxxxxxxxxxxxxxxx
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.

"Douglas J Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:1B77DE3A-8954-4AD1-9C51-D1F72D69FB40@xxxxxxxxxxxxxxxx
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?

"Allen Browne" wrote:

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

"Ron Weaver" <RonWeaver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:DF89365C-5959-4F60-B3AA-ECDBCFAAD89A@xxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: Booking duplicates
    ... There is no error message and the code compiles. ... fields, then you go into a sub form that has the product in it, with a drop ... Allen Browne - Microsoft MVP. ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Subform Totals
    ... In the main form I had some code which restricted the source of the subform ... For each of these fields their is a total field in the form footer. ... The sub form is a bound to a view. ... The questions is why do the total fields in the form footer of the sub ...
    (microsoft.public.access.adp.sqlserver)
  • Re: standard rel. rules between 2 main frms with 1-1 rel. and su
    ... TblAnswersToQuestions - Each responders to one or more of your ... AnswersToQuestionsID ... I set up an If statement to send the tab to a sub form in the Sometimes ... but Ctrl+Tab out of the subform into ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Updating a field property
    ... frmCall Tracking - Issues Log>> SUB FORM ... in a subform, you have to reference the subform control name and it's form ... With your form in design view Select Format, Conditional Formatting ... fields) and the value you want to check for to change the backcolor to white. ...
    (microsoft.public.access.formscoding)
  • Re: Help!! Run-time error 2455 : ....invalid reference to the property Form/Report
    ... what data that is shown in the sub form not the other way around. ... It displays 20 records on the subform. ... I don't believe a macro is the best method. ... main form to alter the value of an unbound option group on the subform. ...
    (microsoft.public.access.forms)