Rental System

[Guest]

I was wondering if anybody could provide some "heads-up" assistance before I proceed on some DB development

I am attempting to create a DB that allows a user to rent a piece of inventory for an alloted time period. Basically, I have 2 tables created, 1 each for the Inventory and the Renter. Additionally, I have created a Table that will handle the Transactions (i.e. When a renter rents an item and returns the item).

I created a system similar to this one before but I had to use a lot of code and that made things a bit difficult for others. Is there an easier way to allow the operator to select a renter, choose the item to be rented, and then record that transaction in the Transaction table? All I basically need is the TransID, Renter Name, Item Name and the date rented. I already have those fields in the table, but unsure of how to develop the form that will allow the operator to rent the item and then record another transaction when the item is returned.

Thanks,

 

[Albert D. Kallal]

I would have:

tblRenters - this would be the table of client info. You could use this
over and over for each time a client makes a rental. (it is nice on the
phone etc...as you don't have to re-type info).

tblInventory - This would be your table of inventory items.

tblOneRental.

This table would be your main application form. You whack a add new rental
button. Up comes a nice clean form (new record). You then select who the
Renter is going to be (from table Renters). Of course this selection would
only be a id that gets sorted. You likely could use a combo box for this. If
the renter is not in "list", then you of course the "not in list" event to
pop up the tblRenters form, add the renter, and then close right back to the
rental form.

Other information on this rental form could be things like terms, where to
deliver the stuff. Who took the rent (which office staff). Of course, if
the Renter needs to rent multiple items at a given time,t hen course you
need one more table:

tblItemsRented (this is your transactions table).

Thus, you then simply enter the items to rent in this nice sub form (a
continues sub form based on tblItemsRented). You of course use a combo box
to select the inventory item, and for the start date, and end date, you
should pop up a nice calendar form that lets the user select the start,a nd
the end date. When the user selects the dates...you simply do a query to see
if the item is rented.

If you use a date range query..ten you DO NOT HAVE to write very much code
at all to check items in, or out. For example, if a user changes their mind,
you can simply delete a record in the sub-form (highlight the record...and
whack del key). This works very well, sinc e NO additional code needs to be
written when users change the date range, or even delete a item in the list
of items rented.

To check for a collision, you simple use:

select ivid from tblItemsRented
where RequestedStartDate <= EndDateField
and
RequestedEndDate >= StartDateField
and
RequestedIventoryID = InventoryID

If the above returns a record..then it is already booked.

strSql = "above sql"

set rstCollsions = currentdb.OpenReocrdSet(strSql)

if rstCollsions.RecordCount > 0 then

msgbox "can no book...


As you can see, it is only a few lines of code to check for a collision, but
deleting, and remove a booking will take no code except simply deleting the
record. This approach as mentioned saves tons of code...

 

[Guest]

Thanks very much for the information. Extremely useful

I will give it a go and get back to you if any problems occur

Thanks again,

 

[nicecait]

very useful. thank you