prevent double booking hired equipmnt iin an Access file

[Guest]

Like with a Hotel Reservations database there must be a way of eliminating
double booking a room to 2 different people at the same time. I have a hire
database and want a warning to appear (msgbox) if a piece of equipment is
already hired out, that way there can be no double booking. All help
appreciated.

I have a main form with client details, linked to the subform via client id.

The subform has

Cloth_Id ClothDrapeName DateHiredOut DueReturnDate

Currently I can keep entering the same cloth again and again, but need
warning set up to stop this.

Mary

 

[stefan hoffmann]

hi Mary,

I have a main form with client details, linked to the subform via client id.
The subform has
Cloth_Id ClothDrapeName DateHiredOut DueReturnDate

Currently I can keep entering the same cloth again and again, but need
warning set up to stop this.

You need to test for overlapping dates:

SELECT * FROM

AS [to]
WHERE to.Cloth_ID In (
SELECT ti.Cloth_ID FROM

ti
WHERE to.DateHiredOut > ti.DateHiredOut
AND to.DateHiredOut < ti.DueReturnDate
);



mfG
--> stefan <--

 

[Vsn]

For sure new rentals, you possibly need to add a field ReturnDate (The date
the unit has confimed returned) than use a query which only shows the units
from which their last record, has the ReturnDate field found not to be NULL.

For atticipations you can use a query looking for DueReturnDate being smaler
or equal (?) to a possible new rental date.

A combination is possible making a query which creates two virtual fields,
one [Available] (to be ReturnDate or if this field is empty to be the
DueReturnDate), this field should than be smaler or equal to a possible new
rental date, the other field could be [Advice] which is "Returned" on
ReturnDate found or "Expexted" when ReturnDate is NULL. Your possible new
rental date should than be greater or equal as the [Available] with the
[Advice] showing on a field on the form.

Ludovic

 

[Guest]

Thank you Stefan, I appreciate your reply and will commence to work through
the code.

regards
Mary

hi Mary,

I have a main form with client details, linked to the subform via client id.
The subform has
Cloth_Id ClothDrapeName DateHiredOut DueReturnDate

Currently I can keep entering the same cloth again and again, but need
warning set up to stop this.

You need to test for overlapping dates:

SELECT * FROM

AS [to]
WHERE to.Cloth_ID In (
SELECT ti.Cloth_ID FROM

ti
WHERE to.DateHiredOut > ti.DateHiredOut
AND to.DateHiredOut < ti.DueReturnDate
);



mfG
--> stefan <--

 

[Guest]

Hi Ludovic

Thanks for taking the time to answer my problem, I will give your advice a go.
Much appreciated.

Mary

For sure new rentals, you possibly need to add a field ReturnDate (The date
the unit has confimed returned) than use a query which only shows the units
from which their last record, has the ReturnDate field found not to be NULL.

For atticipations you can use a query looking for DueReturnDate being smaler
or equal (?) to a possible new rental date.

A combination is possible making a query which creates two virtual fields,
one [Available] (to be ReturnDate or if this field is empty to be the
DueReturnDate), this field should than be smaler or equal to a possible new
rental date, the other field could be [Advice] which is "Returned" on
ReturnDate found or "Expexted" when ReturnDate is NULL. Your possible new
rental date should than be greater or equal as the [Available] with the
[Advice] showing on a field on the form.

Ludovic

Like with a Hotel Reservations database there must be a way of eliminating
double booking a room to 2 different people at the same time. I have a
hire
database and want a warning to appear (msgbox) if a piece of equipment is
already hired out, that way there can be no double booking. All help
appreciated.

I have a main form with client details, linked to the subform via client
id.

The subform has

Cloth_Id ClothDrapeName DateHiredOut DueReturnDate

Currently I can keep entering the same cloth again and again, but need
warning set up to stop this.

Mary