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

--
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" <(E-Mail Removed)> wrote in message
newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> 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

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
>
> --
> 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" <(E-Mail Removed)> wrote in message
> newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> 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
> >
> > --
> > 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" <(E-Mail Removed)> wrote in message
> > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > > 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
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
> news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> > 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
> > >
> > > --
> > > 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" <(E-Mail Removed)> wrote in message
> > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:71E7A665-A729-4C08-980E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> > > 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
> > > >
> > > > --
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
> news:71E7A665-A729-4C08-980E-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> > > > 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
> > > > >
> > > > > --
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:71E7A665-A729-4C08-980E-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> > > > > 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
> > > > > >
> > > > > > --
> > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:9CBDC410-0031-4DE8-87E1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> > news:71E7A665-A729-4C08-980E-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)> wrote in message
>> > > > news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
>> > > > > 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
>> > > > > >
>> > > > > > --
>> > > > > > 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" <(E-Mail Removed)> wrote in
>> > > > > > message
>> > > > > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
>> > > > > > > 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
>> > > > > >
>> > > > > >
>> > > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >

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" <(E-Mail Removed)> wrote in message
> news:9CBDC410-0031-4DE8-87E1-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> > news:71E7A665-A729-4C08-980E-(E-Mail Removed)...
> >> > > 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" <(E-Mail Removed)> wrote in message
> >> > > > news:1B77DE3A-8954-4AD1-9C51-(E-Mail Removed)...
> >> > > > > 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
> >> > > > > >
> >> > > > > > --
> >> > > > > > 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" <(E-Mail Removed)> wrote in
> >> > > > > > message
> >> > > > > > newsF89365C-5959-4F60-B3AA-(E-Mail Removed)...
> >> > > > > > > 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
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > >
> >> > > >
> >> > > >
> >> >
> >> >
> >> >
>
>
>