Time checking problem

so do u suggest renaming the boxes?
if so how could the new code look - i really don't know which is bound to
the box name and which is bound to the field in the code as stated below..

Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut =
Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or
IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " &
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
vbYes Then
Cancel = True
End If
End If
End If


Thanks.
Neil


"Douglas J Steele" wrote in message
...
FWIW, I always rename all controls on my forms. I'd have the textboxes
named
txtTimeIn and txtTimeOut, just to ensure that there's no confusion between
the controls and the fields.

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


"Neil M" wrote in message
...
I found a small error in the coding and changed it but now when runs it
erroes on this line;

If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut =
Me.TimeOut.OldValue)
And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or
IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then

The first Me.TimeOut is highlighted on the error.

I have check the box names and they are named TimeIn and TimeOut so it
should be working?


Regards,


Neil

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime)
&
")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID
=
"
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2,
"Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the
boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not
contain
Date as far as I know as they use a input mask so the user inputs
xx:xx

They are also no events other than the once listed above
(BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or the
form?

Neil



"Douglas J Steele" wrote in
message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields, do
they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time
(Short
Time format)

I still get the error stating that the time in question is
duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data
not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how it
interpreted
it. Use that in the WHERE clause of a query to see what's going
on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and
EndDateTime
in
the
example) that contain both the date and time? Are they Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter this
data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something like
this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " & Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime,
strcJetDateTime)
&
_
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking",
strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult &
vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to
crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the check
the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any
times
on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am 9:00am,
and
I
want
to
input a new record later on for the same day and room for
9:30am,
I
want
the
program to tell me that it has already been booked for that
time.
It
can
allow me to continue and book as at this stage I just want it
to
check
the
time for me?

I would appreciate any suggestions or help as I do not
really
have
a
clue
how this can be done? (my database knowledge is better than
normal
but
not
quite advanced!)

Regards,

Neil

Realistically, you're using the textboxes everywhere in your code, not the
recordset fields.

BTW, you've got a Debug.Print strWhere in your code. What does strWhere look
like? (If you're not familiar with Debug.Print, use Ctrl-G to go to the
Immediate Window. Copy a line or two to the clipboard, and paste it back
here)

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


"Neil M" wrote in message
...
so do u suggest renaming the boxes?
if so how could the new code look - i really don't know which is bound to
the box name and which is bound to the field in the code as stated below..

Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut =
Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or
IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = "
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
vbYes Then
Cancel = True
End If
End If
End If


Thanks.
Neil


"Douglas J Steele" wrote in message
...
FWIW, I always rename all controls on my forms. I'd have the textboxes
named
txtTimeIn and txtTimeOut, just to ensure that there's no confusion
between
the controls and the fields.

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


"Neil M" wrote in message
...
I found a small error in the coding and changed it but now when runs
it
erroes on this line;

If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut =
Me.TimeOut.OldValue)
And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or
IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then

The first Me.TimeOut is highlighted on the error.

I have check the box names and they are named TimeIn and TimeOut so it
should be working?


Regards,


Neil

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut,
strcJetDateTime)
&
")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND
(RoomID
=
"
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2,
"Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the
boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not
contain
Date as far as I know as they use a input mask so the user inputs
xx:xx

They are also no events other than the once listed above
(BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or
the
form?

Neil



"Douglas J Steele" wrote in
message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields,
do
they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both
Date/Time
(Short
Time format)

I still get the error stating that the time in question is
duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data
not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how
it
interpreted
it. Use that in the WHERE clause of a query to see what's
going
on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and
EndDateTime
in
the
example) that contain both the date and time? Are they
Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter
this
data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something
like
this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " &
Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime,
strcJetDateTime)
&
_
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking",
strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult &
vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to
crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the
check
the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any
times
on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am
9:00am,
and
I
want
to
input a new record later on for the same day and room for
9:30am,
I
want
the
program to tell me that it has already been booked for that
time.
It
can
allow me to continue and book as at this stage I just want
it
to
check
the
time for me?

I would appreciate any suggestions or help as I do not
really
have
a
clue
how this can be done? (my database knowledge is better
than
normal
but
not
quite advanced!)

Regards,

Neil

Neil, is this database designed to work for just one day? Would two
appointments for 9am be a duplicate? Or do you have another date field here
somewhere?

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

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = "
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not contain
Date as far as I know as they use a input mask so the user inputs xx:xx

They are also no events other than the once listed above (BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or the
form?

Neil



"Douglas J Steele" wrote in message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields, do they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time
(Short
Time format)

I still get the error stating that the time in question is duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how it
interpreted
it. Use that in the WHERE clause of a query to see what's going on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and EndDateTime in
the
example) that contain both the date and time? Are they Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter this data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something like this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " & Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime, strcJetDateTime) & _
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the check the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any times on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I
want
to
input a new record later on for the same day and room for 9:30am,
I
want
the
program to tell me that it has already been booked for that time.
It
can
allow me to continue and book as at this stage I just want it to
check
the
time for me?

I would appreciate any suggestions or help as I do not really
have
a
clue
how this can be done? (my database knowledge is better than
normal
but
not
quite advanced!)

Regards,

Neil

yes its one day.
so the user selects the date at the top and the room and there is a subform
below that displays/allows input for the times the Staff wants to book the
room in and out.

Yes two appointments say for 9am would be duplicated but the test is for it
not to be duplicated on the SAME day. Whereas you may have 9am for an
appointment on the 1st nov and one also on the 2nd nov which should not
bring up the error.

Hope this helps


General Structu

(MAIN) DATE ----
ROOM ----
STAFF / TIME IN / TIME OUT (MAINSUB)




"Allen Browne" wrote in message
...
Neil, is this database designed to work for just one day? Would two
appointments for 9am be a duplicate? Or do you have another date field
here
somewhere?

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

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) &
")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID =
"
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2,
"Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not
contain
Date as far as I know as they use a input mask so the user inputs xx:xx

They are also no events other than the once listed above (BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or the
form?

Neil



"Douglas J Steele" wrote in message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields, do
they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time
(Short
Time format)

I still get the error stating that the time in question is
duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how it
interpreted
it. Use that in the WHERE clause of a query to see what's going on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and EndDateTime
in
the
example) that contain both the date and time? Are they Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter this
data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something like
this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " & Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime, strcJetDateTime) &
_
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult &
vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to
crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the check
the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any times
on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am 9:00am, and
I
want
to
input a new record later on for the same day and room for
9:30am,
I
want
the
program to tell me that it has already been booked for that
time.
It
can
allow me to continue and book as at this stage I just want it to
check
the
time for me?

I would appreciate any suggestions or help as I do not really
have
a
clue
how this can be done? (my database knowledge is better than
normal
but
not
quite advanced!)

Regards,

Neil

So how do you plan to incorporate the date into the strWhere string which
looks up the table for duplicates?

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

"Neil M" wrote in message
...
yes its one day.
so the user selects the date at the top and the room and there is a
subform
below that displays/allows input for the times the Staff wants to book the
room in and out.

Yes two appointments say for 9am would be duplicated but the test is for
it
not to be duplicated on the SAME day. Whereas you may have 9am for an
appointment on the 1st nov and one also on the 2nd nov which should not
bring up the error.

Hope this helps


General Structu

(MAIN) DATE ----
ROOM ----
STAFF / TIME IN / TIME OUT
(MAINSUB)




"Allen Browne" wrote in message
...
Neil, is this database designed to work for just one day? Would two
appointments for 9am be a duplicate? Or do you have another date field
here
somewhere?

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) &
")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID =
"
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2,
"Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not
contain
Date as far as I know as they use a input mask so the user inputs xx:xx

They are also no events other than the once listed above
(BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or the
form?

Neil



"Douglas J Steele" wrote in message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields, do
they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time
(Short
Time format)

I still get the error stating that the time in question is
duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how it
interpreted
it. Use that in the WHERE clause of a query to see what's going
on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and EndDateTime
in
the
example) that contain both the date and time? Are they Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter this
data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something like
this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " & Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime, strcJetDateTime)
&
_
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult &
vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to
crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the check
the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any times
on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am 9:00am,
and
I
want
to
input a new record later on for the same day and room for
9:30am,
I
want
the
program to tell me that it has already been booked for that
time.
It
can
allow me to continue and book as at this stage I just want it
to
check
the
time for me?

I would appreciate any suggestions or help as I do not really
have
a
clue
how this can be done? (my database knowledge is better than
normal
but
not
quite advanced!)

i haven't gave it a 2nd thought - someone on the thread completed the code
for me i just wanted to get it working then bbreak it down to see how it
worked so i could explain it to others on the forums.

can you help any more please?



"Allen Browne" wrote in message
...
So how do you plan to incorporate the date into the strWhere string which
looks up the table for duplicates?

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

"Neil M" wrote in message
...
yes its one day.
so the user selects the date at the top and the room and there is a
subform
below that displays/allows input for the times the Staff wants to book
the
room in and out.

Yes two appointments say for 9am would be duplicated but the test is for
it
not to be duplicated on the SAME day. Whereas you may have 9am for an
appointment on the 1st nov and one also on the 2nd nov which should not
bring up the error.

Hope this helps


General Structu

(MAIN) DATE ----
ROOM ----
STAFF / TIME IN / TIME OUT
(MAINSUB)




"Allen Browne" wrote in message
...
Neil, is this database designed to work for just one day? Would two
appointments for 9am be a duplicate? Or do you have another date field
here
somewhere?

"Neil M" wrote in message
...
right here is the code for BeforeUpdate:

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

If ((Me.TimeIn = Me.TimeIn.OldValue) And _
(Me.TimeOut = Me.TimeOut.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _
Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime)
&
")
AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID
=
"
&
Me.RoomID & ")"
Debug.Print strWhere
varResult = DLookup("ID", "TBL_Main", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult & vbCrLf &
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2,
"Double-booked")
vbYes Then
Cancel = True
End If
End If
End If
End Sub


The TimeIn and TimeOut are both bound to their fields and are the
boxes
are
named the same.
Both are Time/Date but format is just Short Time so no they do not
contain
Date as far as I know as they use a input mask so the user inputs
xx:xx

They are also no events other than the once listed above
(BeforeUpdate),
should these be linked to the BeforeUpdate on the actual field or the
form?

Neil



"Douglas J Steele" wrote in
message
...
I'm not sure you've really answered Allen's question.

While you said that TimeIn and TimeOut are both Date/Time fields, do
they
contain both the date and time, or do they only contain the time?

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


"Neil M" wrote in message
...
Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time
(Short
Time format)

I still get the error stating that the time in question is
duplicated.
Although it hasn't.
The correct time is shown on the error displayed.

Now I also get the debug window come up with the "Method or Data
not
found"
and the following is highlighted (OldValue) in;

If ((Me.TimeIn = Me.TimeIn.OldValue) And


Any suggestions?

Thanks,

Neil


"Allen Browne" wrote in message
...
Add the line:
Debug.Print strWhere
just before the "varResult = " line.
After it runs, open the Immediate Window (Ctrl+G) to see how it
interpreted
it. Use that in the WHERE clause of a query to see what's going
on.
Does
the
booking number that it reports make any sense?

Does your table have 2 fields (named StartDateTime and
EndDateTime
in
the
example) that contain both the date and time? Are they Date/Time
fields?

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

"Allen Browne" wrote in message
...
Use the BeforeUpdate event of the *form* where you enter this
data
to
provide the warning if there is an overlap.

The basic concept is that 2 events overlap if:
- A begins before B ends, AND
- B begins before A ends.

The form's BeforeUpdate event procedure will be something like
this
aircode:

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

If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _
(Me.EndDateTime = Me.EndDateTime.OldValue) AND _
(Me.RoomID = Me.RoomID.OldValue)) OR
IsNull(Me.StartDateTime)
_
OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then
'do nothing
Else
strWhere = "(StartDateTime " & Format(Me.EndDateTime,
strcJetDateTime) & _
") AND " & Format(Me.StartDateTime,
strcJetDateTime)
&
_
" EndDateTime) AND (RoomID = " & Me.RoomID & ")
varResult = DLookup("BookingID", "tblBooking",
strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with booking # " & varResult &
vbCrLf
&
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2,
"Double-booked")

vbYes Then
Cancel = True
End If
End If
End If
End Sub


If you need to cope with open-ended bookings, or need to
crosscheck
all
bookings against each other, 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.

"Neil M" wrote in message
...
Hi all (yet again!)

I have a problem with the way my database will have the check
the
following.

Room Date Time In and Time Out

I want to make sure that the inputter doesn't overlap any
times
on
the
same
day for the room (there are 5 rooms)
So say I have booked one room on 1/1/05 for 8:00am 9:00am,
and
I
want
to
input a new record later on for the same day and room for
9:30am,
I
want
the
program to tell me that it has already been booked for that
time.
It
can
allow me to continue and book as at this stage I just want it
to
check
the
time for me?

I would appreciate any suggestions or help as I do not
really
have
a
clue
how this can be done? (my database knowledge is better than
normal
but
not
quite advanced!)

It looks like you have not given us enough information to help you here. The
solution will depend on which fields are in which tables, and what they are
called. Presumably you will need to combine the date and time values somehow
by adding the time value on to the date value.

That's probably as far as I can take you here. Hopefully you will be able to
use the concepts explained and apply them as your understanding grows.

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

"Neil M" wrote in message
...
i haven't gave it a 2nd thought - someone on the thread completed the code
for me i just wanted to get it working then bbreak it down to see how it
worked so i could explain it to others on the forums.

can you help any more please?