Eliminate Double Booking

[Maxine2006]

Hi everyone, I have a database that is essentially used to record hiring out
and return of equipment.

The only part that I cannot get to work is that it allows the same piece of
equipment to be hired out when it has already been booked by someone else.
This means that the 2nd person is disappointed and does not get to hire out
the equipment.

The fields I have are the:

Equip ID Equip Name Dated Hired Out Due Date for Return
Actual Date Returned.

(The Equip Id automatically looks up the Equip Name and fills it in, then I
enter the Date Hired Out and Due Date of Return, the Actual Dated Returned
is only filled in when the equipment is returned)

What I need is some way of being alerted if the Equip. is already hired out
when I enter the Equip Id, e.g

M1 Marquee 12/6/06 21/6/06


No other customer can have this Marquee during the period 12 June to 21 June
2006.

I would really appreciate any help on this problem, the database is not very
big and is available if needed.

thanks in advance
Maxine

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form to see if the equipment is
already out.

Two hirings overlap if:
- A starts before B ends, and
- B starts before A ends, and
- they are not the same hiring.

Presumably the equipement is still out until Actual_Return_Date is no longer
null, so you could use Nz() to give a very-future date like this:
Nz([Actual_Return_Date], #1/1/2999#)

 

[SusanV]

Hi Maxine,

Replace the stuff in the code below with the actual field, table and control
names:

--
hth,
SusanV



Private Sub YourButton_Click()

Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim eq as String
Dim eqName as String
Dim out as Date
Dim due as Date
Dim strSQL as String

Set cnx = CurrentProject.Connection

eq = MyForm.IdControl
eqName = MyForm.NameControl
out = MyForm.DateOutControl
due = MyForm.DueDateControl

strSQL = "SELECT * FROM MyTable " _
& "WHERE [Equip ID] = " & eq & " AND " _
& "[Equip Name] = " & eqName & " AND " _
& "[Dated Hired Out ] = " & out & " AND " _
& "[Due Date for Return] = " & due

rs.Open strSQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

If rs.RecordCount = 0 Then
MsgBox "Duplicate Rental!" 'Or whatever you want to tell the user
Exit Sube
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

rs.Close

End Sub

 

[SusanV]

Oops, I did the If statement backwards - should be:
If rs.RecordCount > 0 Then

TGIF <sigh>

Goodluck

Hi Maxine,

Replace the stuff in the code below with the actual field, table and
control names:

--
hth,
SusanV



Private Sub YourButton_Click()

Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim eq as String
Dim eqName as String
Dim out as Date
Dim due as Date
Dim strSQL as String

Set cnx = CurrentProject.Connection

eq = MyForm.IdControl
eqName = MyForm.NameControl
out = MyForm.DateOutControl
due = MyForm.DueDateControl

strSQL = "SELECT * FROM MyTable " _
& "WHERE [Equip ID] = " & eq & " AND " _
& "[Equip Name] = " & eqName & " AND " _
& "[Dated Hired Out ] = " & out & " AND " _
& "[Due Date for Return] = " & due

rs.Open strSQL, cnx, adOpenKeyset, adLockOptimistic, adCmdText

If rs.RecordCount = 0 Then
MsgBox "Duplicate Rental!" 'Or whatever you want to tell the user
Exit Sube
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

rs.Close

End Sub

Hi everyone, I have a database that is essentially used to record hiring
out
and return of equipment.

The only part that I cannot get to work is that it allows the same piece
of
equipment to be hired out when it has already been booked by someone
else.
This means that the 2nd person is disappointed and does not get to hire
out
the equipment.

The fields I have are the:

Equip ID Equip Name Dated Hired Out Due Date for Return
Actual Date Returned.

(The Equip Id automatically looks up the Equip Name and fills it in, then
I
enter the Date Hired Out and Due Date of Return, the Actual Dated
Returned
is only filled in when the equipment is returned)

What I need is some way of being alerted if the Equip. is already hired
out
when I enter the Equip Id, e.g

M1 Marquee 12/6/06
21/6/06


No other customer can have this Marquee during the period 12 June to 21
June
2006.

I would really appreciate any help on this problem, the database is not
very
big and is available if needed.

thanks in advance
Maxine