Equipment Booking

[cadillac]

Hi

I am curently doing an access data base to book audiovisual equipments. I have used index to have date, equipmet and startTime as primary key that way I dont book the same equipment twice. But am lost with the ReturnTime because if the equipment is out from 8am to 11am it lets me book from 9am to 10am for example. My times are selected from combobox which are fed by tables. My tables are the following.

tblUserInfo
ID_PR(primary key)
UserName
UserID

tblLoanInfo
ID_SC(primary key)
ID_PR
LoanDate
Equipment
StartTime
EndTime

tblEquipment
Equipment

tblStartTime
StartTime

tblReturnTime
ReturnTime


I have a frmLoan with the user information and the loanInfo subform with the loan date, equipment, starttime and endtime. I am stuck trying to figure out how to prevent double booking for time gaps.

[nico5038]

As you found out, the starttime in the index won't work.
When inserting a new appointment you'll need to test or for this equipment ID there's an overlap with an existing booking.

An alternative might be to "translate" the times of a day into 24 red/green colored text boxes. Thus the availability is also visualized and when you really want it flexible, even the time selection could be done with the "OnClick" on these boxes...

Nic;o)

[cadillac]

Thanks for the info nico. I am trying to find a way that after I select the date and equipment, and selecting the starttime a code can verify if the selected starttime is >= than the returntime. I have a query that pulls out the date and equipment from the form, just lost on how to do the comparison.

[cadillac]

I have done a qry to find overlap time and see double booking. I need some help on how to use the qry to prevent such events. The qry looks like this.

SELECT tblloaninfo.LoanDate, tblloaninfo.EquipmentID, tblloaninfo.StartTime, tblloaninfo.Returntime, tblloaninfo_1.LoanDate, tblloaninfo_1.EquipmentID, tblloaninfo_1.StartTime, tblloaninfo_1.Returntime, ([tblloaninfo_1].[Starttime]>=[tblloaninfo].[Returntime]) Or ([tblloaninfo_1].[Returntime]<=[tblloaninfo].[Starttime]) Or ([tblloaninfo].[ID_SC]=[tblloaninfo_1].[ID_SC]) AS NoClash
FROM tblloaninfo, tblloaninfo AS tblloaninfo_1
WHERE (((tblloaninfo.LoanDate)=[Forms]![frmLoan]![loaninfo Subform].[Form]![loandate]) AND ((tblloaninfo.EquipmentID)=[Forms]![frmLoan]![loaninfo Subform].[Form]![EquipmentID]) AND ((tblloaninfo_1.LoanDate)=[Forms]![frmLoan]![loaninfo Subform].[Form]![loandate]) AND ((tblloaninfo_1.EquipmentID)=[Forms]![frmLoan]![loaninfo Subform].[Form]![EquipmentID]) AND ((([tblloaninfo_1].[Starttime]>=[tblloaninfo].[Returntime]) Or ([tblloaninfo_1].[Returntime]<=[tblloaninfo].[Starttime]) Or ([tblloaninfo].[ID_SC]=[tblloaninfo_1].[ID_SC]))=False))
ORDER BY tblloaninfo.StartTime;
 

any ideas...

[nico5038]

You'll need to test the double booking on your form, thus I would propose to use an IF statement using a DLOOKUP like:

IF ISNULL(DLOOKUP("EquipmentID","tblloaninfo","#" & Me.Loandate & " " & Me.StartTime & "# < Loandate + Returntime AND #" & Me.Loandate & " " & Me.Returntime & "# > Loandate + StartTime)") then
   ' No match action
else
   ' match action
endif
 

The test is to find an overlap from the period with an existing period. This is done by first concatenating the date and time from the form and by adding the date and time of the table.
When the form's startdate is before the enddatetime of a record and the returndate is after the startdatetime from the record, than a record is found and thus inserting this booking isn't possible.

Getting the idea ?

Nic;o)

[cadillac]

I have been working with these two and can not get one to work. Help please...

Private Sub Returntime_AfterUpdate()
If Not IsNull(DLookup("[EquipmentID]", "tblloaninfo", "[Starttime] >= #" & Me.
Returntime & "#AND [Returntime] <= #" & Me.StartTime & "#")) Then
  MsgBox Me.EquipmentID & " is booked for the selected time, please select
another Equipment or time!", vbOKOnly
Else
  DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

and I also found this one and gave it a try

Private Sub Returntime_AfterUpdate()
Dim strWhere As String
  Dim varResult As Variant
  Dim strMsg As String
  Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
 
  If ((Me.StartTime = Me.StartTime.OldValue) And (Me.Returntime = Me.
Returntime.OldValue) And (Me.EquipmentID = Me.EqipmentID.OldValue)) Or IsNull
(Me.StartTime) Or IsNull(Me.Returntime) Or IsNull(Me.EquipmentID) Then
          'do nothing
  Else
      strWhere = "(StartTime < " & Format(Me.Returntime, strcJetDateTime) &
")" AND " & Format(Me.StartTime, strcJetDateTime) & " < ReturnTime) AND
(EquipmentID = " & Me.EquipmentID & ")"
Debug.Print strWhere
      varResult = DLookup("ID_SC", "tblloaninfo", 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

thank you for you help...

[Stewart Ross]

Hi Cadillac. Please use the code tags we provide so that your code is easier to read.

Telling us that something does not work is not much of a clue. What do the two routines you have tried do when you try them? Unless you can be specific we can't help you, as we are not in a position to understand the exact context in which you are using your data. Give us examples of the bookings involved.

When I've done similar applications (for booking classrooms at particular times, seeking to avoid clashes with other bookings) I have written out the combinations that are possible and shown them on a time-line, then worked out from these notes the conditional tests needed to ensure that the bookings do not clash. You may well need to do the same.

To give you some idea of whats involved, if you have a definite booking for a piece of AV equipment between a particular startdate-time and enddate-time then no other booking for that piece of equipment can be made where:

(1) the start time for the new booking is between an existing booking startdate-time value and the enddate-time value, or

(2) the enddate-time value of the new booking is between an existing startdate-time value of the existing booking and the enddate-time value of that booking, or

(3) the startdate-time value of the new booking is less than the startdate-time value of an existing booking and the enddate-time value of the new booking is greater than the enddate-time value of an existing booking

To put it another way, you can't allow equipment bookings starting or ending within a current booking period, or bookings which encompass the whole of an existing booking.

If an item is out and the booking for it is still 'open' - no end date-time recorded - then that is something else you will have to take into account in applying these tests.

-Stewart

[nico5038]

In both "solutions" there's no date compared, thus it won't work.
Comparing with the .OldValue is only interestiung when you change data on a form, and you need to compare with al records...

Please check my proposed solution again.

Nic;o)

[cadillac]

I was thinking that taking the posible booking values from the form, compare them to the tables and if there was no clash then save to table. Going back to your code I get an error. The debug gives me this line in red.

IF ISNULL(DLOOKUP("EquipmentID","tblloaninfo","#" & Me.Loandate & " " & Me.StartTime & "# < Loandate + Returntime AND #" & Me.Loandate & " " & Me.Returntime & "# > Loandate + StartTime)") then

thanks for your help...

[nico5038]

Can you make a .zip with a sampe .mdb with the table and some testdate, thus I can check the error.
Just use the [Go Advanced] button to add an attachment.

Nic;o)

[cadillac]

Once again thanks Nico for your help. Here is a copy of my sample data base. There you will see a qry named qrybookingclash which shows my double bookings also I have a qrybooking which lets me see the booking all have a criteria that takes the date and equipment from the open frmLoan. I am desperate to get the double booking posibility out. I will appreciate all you can do to help. Thank you.

[nico5038]

OK here a working sample, I embedded the DLOOKUP and corrected the erroneous ")". Also added the EquipmentIS and ID_SC to make the DLOOKUP 100%.
The test has been placed in a separate sub, as it needs to be executed after a change of either time combo.
Also added a switch to control the cancellation of the update when no overlap is allowed by the user.

Nic;o)

[cadillac]

Thank very much for all your help. I really appreciate all you have done to help me out. The Dlookup works 100% just as say.

[cadillac]

I dont know if its to much to ask but can it be possible for you to tell me how to change the option of the Double-Booked button options. I dont want to mess it up, since its working perfectly. What i would like it for to change the pop up message from yes no options to one. In other words eliminate the option to answer a yes to continue any way.

Else
strMsg = "Clashes with booking times: " & DLookup("[Starttime] & ' / ' & [Returntime]", "tblloaninfo", "#" & Me.LoanDate & " " & Me.StartTime & "# < Loandate + Returntime AND #" & Me.LoanDate & " " & Me.Returntime & "# > Loandate + StartTime AND EquipmentID='" & Me.EquipmentID & "'") & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") = vbNo
Then
switchOverlap = True
End If

[cadillac]

Nico
I went ahead a gave it a try and I got it working like I had asked. Can you give it a look to see if the changes are ok...

Else
strMsg = "Clashes with booking times: " & DLookup("[Starttime] & ' / ' & [Returntime]", "tblloaninfo", "#" & Me.LoanDate & " " & Me.StartTime & "# < Loandate + Returntime AND #" & Me.LoanDate & " " & Me.Returntime & "# > Loandate + StartTime AND EquipmentID='" & Me.EquipmentID & "'")
If MsgBox("Double-booked") = vbOK
Then
switchOverlap = True
End If

[nico5038]

You don't need to use the "IF" for the messagebox, as an answer isn't needed and just a "msgbox strMsg" will do.
Just keep setting the "switchOverlap = True" when there's an overlap to reset the data when the user navigates to another record as tested in the "IF DLOOKUP".

Nic;o)

[cadillac]

Thanks for the information.