Eliminate Double Booking

  • Thread starter Thread starter Maxine2006
  • Start date Start date
M

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
 
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#)
 
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
 
Oops, I did the If statement backwards - should be:
If rs.RecordCount > 0 Then

TGIF <sigh>

Goodluck

SusanV said:
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



Maxine2006 said:
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
Click to expand...
Click to expand...
 
Back
Top