Check Out Condition Expression

[Gus]

Hello,

Thanks for your time and help.

I have a Database which has Three Tables tbl_Users, tbl_Agreements,
tblAgr_Check_Out
The User Table has these attributes User_ID, User_FName, User_FLame

tbl_Agreements has Agr_Num this is also the Primary Key on this table and

tblAgr_Check_Out has ChKO_ID, Checked_Out, Date_Checked_Out, User_ID, Agr_Num

I created a form so that users will enter information about an agreement(s)
they have at their desk. If another user is looking for an agreement by
seeing it as checked out they will know where to look an who to ask for
information. I noticed that when testing the Form Check_Out_Form and in my
table tblAgr_Check_Out the same person can have one agreement checked out
multiple times and also any other user can as well have the same agreement
checked out. This is a problem.

I need to have the form prevent Users from selecting and agreement that is
currently checked. I believe that this will prevent the same user also not
being able to check out the same agreement more than once. If you have
suggesting about how I can change my table structure for accomplishing my
database purpose it would also help.

Any help would be very appreciated.

 

[Allen Browne]

In tblAgr_Check_Out, you don't have any mechanism for checking it back in
again, so I assume you delete the record when the user returns it?

If so, you could just add a unique index on tblAgr_Check_Out.Agr_Num. That
will prevent 2 records for the same Agr_Num.

If you do have additional fields you did not show for when it is checked in
again, your question become how to tell if a user tries to book out an
Agr_Num while somone else still has it out. At the most basic level, use the
form's BeforeUpdate event procedure to DLookup() the tblAgr_Check_Out table,
and see if there is a record for the Agr_Num where the Date_Checked_In is
null.

If you want to go further, the logic for looking for overlapping dates is:
Record 1 starts before Record 2 ends, AND
Record 2 starts before Record 1 ends.