Time checking problem

  • Thread starter Thread starter Neil M
  • Start date Start date
N

Neil M

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
 
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
 
that actually checks and errors on every time regardless of whether their is
an overlap?

any suggestions - want me to give you any more info?
 
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?
 
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
 
Do you have a text box named TimeIn, bound to a field that is also named
TimeIn? Or are there different names in use?

The failure of that line could affect what else is happening. Post the
entire event procedure.
 
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?
 
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
 
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

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


Regards,


Neil
 
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.
 
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
 
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)
 
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?
 
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 Structure:

(MAIN) DATE ---->
ROOM ---->
STAFF / TIME IN / TIME OUT (MAINSUB)
 
So how do you plan to incorporate the date into the strWhere string which
looks up the table for duplicates?
 
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?
 
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.
 
Back
Top