rentals

[plonker]

i have a specific problem and i'm wondering does anyone have any ideas on how i would approach this - i have tables for rentals, members and products - i want to implement a rental system from a rental form where i can check if a certain item is available to rent or not - the problem is then when someone returns an item how do i know if it is available to rent or not? i had thought of using a checkbox in the product table which could be checked if it was currently rented and unchecked if not - could this work? is there an easier way? i'd appreciate any comments

Thanks

 

[neileg]

This must be a standard course assignment because it comes up so often!

Don't use a checkbox, or anything on the product table. Use a separate table that shows the dates that the equipment is booked for, ie a booked out and a booked in date. The you can determine if the equipment is hired out by finding the latest booked out date. If the booked in date is null then it's still on hire.

 

[plonker]

forgot to mention that i had that - i have a member rental table which stores the product id, member id, date out and date due - i then ran a query to see if the date due was null i.e the person had not returned it but the problem is with rental history - i have rentals which have been booked out say last week and returned yesterday - that item is now available to rent again? yet it has a return date?

 

[neileg]

I said ...by finding the latest booked out date. If the booked in date is null then it's still on hire.

So don't get a rental history, you get the latest booking only. You can use MAX on the booked out date to find this.

 

[plonker]

i got the query working but now i have another problem - i have a rental form where i can view previous transactions and make new transactions - i've used a subform which contains the transaction details which is inside a form displaying member details - the problem now is that it is displaying all records associated with the member - i want it to show only those that have booked_in date = null ?

 

[neileg]

So add the Is Null criterion to your query.

Or explain why this doesn't work.

 

[plonker]

thanks neileg,

Now is it possible to run both queries (current + past rentals) using the same subform? if i put the queries behind 2 buttons?
Also i've used the code generated by the add new record wizard to create a new transaction yet i can only edit some of the fields? why is this? it's as if i'm locked out of the textboxes- i checked my tables and i havent implemented any constraints?

 

[neileg]

Come on, you're supposed to do this yourself! I'm willing to help but it's your assignment.

You can use the same form you just need to figure out how to change the criterion from Is Null to Is Not Null.

Your new record problem, I have no idea. I don't know what the code is nor what tables or queries you are basing it on.

 

[plonker]

I have the button to run the queries but what i was asking was help with changing the subform - i dont know how to link the command button to the subform

 

[neileg]

The easiest way will be to insert tabs in your form. Add a subform linked to a query with Is Null as the criterion on one tab and to Is Not Null on the other.

Alternatively, if you have the code to change the criterion in the underlying query, then run this code and the requery the subform.