I am producing this 'booking and returning' database for an art dep (just the normal lending out and returning of art equipment. If the student returns the eqipment item late than the teacher has an option to restrict the student from lending out anything for up to three days. Would i need to do this on form level or on within the student table!?!?!
All "levels" are at table level. Forms are for user input, display and data manipulation prior to storing or after retrieving table data. If you could rephrase your quesion better with some more specific info then methinks me can help.
It would be most helpful if you could attach a copy of your database here to better serve you. So far though sounds like a case for an "IF, THEN" type of situation. IF the return date = something, Then, code set a certain field probably. Anyhow it sounds like that's the way you're going. So as asked by ineuw, a much better clarification of your situation. We need to know what you have in your database, what exactly you wish it to do when a certain condition is met, etc. If it's late do you simply with the program to flag the person that it's late and ask if they wish to lend one out again? So many things to know. But hey, we're here to try to help you, so help us out a bit more.
Suppose you have a table called LOANS with fields StudentID, EquipNo, LoanDate, DueDate, ReturnedDate. I think this is all you would need to store in a table. You can derive the most recent “late” date and add 3 days to give you a date to compare to. Then have a form where new loans are entered and use a BeforeUpdate event procedure to check that the LoanDate on the new loan is after the most recent “compare” date.
Hope that helps.
ps this is my first post
Just wanted to step in and say WELCOME to the forum. Hope you enjoy it and keep in mind we are all here to help one another. Also, in times it's best to post a sample database to get better help sooner. Also, in return to give good details to someone in need as we are not all always on the same knowledge level.
First Name (text)
Second Name (text)
I was thinking that when the teacher had signed back in the equipment if the equipment was late than a if statement could be used restricting that student from taking out any equip for 3 days, i just didnt know if it could be done!!?
I was thinking that when the teacher had signed back in the equipment if the equipment was late than a if statement could be used restricting that student from taking out any equip for 3 days
After my first post I wondered if I'd mis-understood the requirements Lucy initially posted. I wondered if Lucy wanted to be able to set a blocking date for the student which would mean having to log a date against the student in the student table (as Bud picked up on). However, I think Lucy's latest clarification can be answered using the validation rule (therefore doesn't need any VB code) using the principle of a Loans table that ineuw and I have previously suggested:
We have a table called LOANS with fields EquipNo, LoanDate, DueDate, ReturnedDate, StudentID (the primary key is EquipNo, LoanDate). I think ineuw's LOANED table would also need EquipNo to be in the primary key.
Assuming you have a form called LoanForm, the following expression can be inserted into the validation rule of the LoanDate entry box properties:
>DMax("[ReturnedDate]","Loans","[ReturnedDate]>[DueDate] and [StudentID]=[Forms]![LoanForm]![StudentID].Value")+3
Assuming a StudentID has been entered, the validation rule will check that for overdue returns, a new loan date is greater than 3 days past the most recent overdue returned date.
Use the Validation text to give an appropriate warning message.
The above does not take account of someone who has overdue items but hasn't returned them yet i.e. the ReturnedDate field is null. But an IIF statement in the above expression should sort that or another validation in the ReturnedDate box on the form.
The [ReturnedDate]>[DueDate] could be replaced by the more powerful DateDiff function as ineuw suggests.
Also, as ineuw mentioned, the LOANS table should be linked to the Student table to give the full relationship.
Thanks for the help I ave already got a booking table which has all the simular fields suggested in the loans table except for the boolean checkbox (sorry i should of mentioned i had a booking table to!) So im thinking that in the booking table should also be the restroction check box??
I think I understand the loaned table now....when the equipment is brought back if the date exceeds the date required back then the restriction would be put upon the student from being able to loan anything out for 3 days. So the loan table would be a reference to when it was returned and who restricting the student from booking anything out???
have a boolean check box type thing on the student table, so when the equipment was signed back in then if it were returned late the check box would be ticked 'freezing' the student account?!?!
Lucy There's several ways of doing what you want. The method I described doesn't need to log the student as frozen because it can work it out from the loan history so don't confuse it with the way you are going. The plus side to it is that it doesn't use VB code. The down side is that it's a bit rigid. See my example LOAN database. The interesting bit is in the properties of the LoanDate box on the form. Try creating a new entry for student A0001 with the loan date as 7th Aug 2004.
Having a flag in the Student table (as suggested by Bud) sounds like the way you are heading. It’s more flexible but slightly trickier to implement IMHO. Are you familiar with VB code ?
If you choose to have a flag then you need to be able to get your database to do the following as I see it:
1) For returns, if the equipment is overdue, set the flag in the Student table.
2) For new loans check if the student's frozen flag is set and if so then check if the 3 day frozen period has expired:
a) If expired, clear the flag and allow the new loan
b) If not expired disallow the new loan
In VB this is fairly easy to implement. Do you want to try this route ?
What fields do you have in your Loans table ?
I've attached another database LOAN2. This uses a query to keep updating the flag in Student table. But it still relies on the history so you need to have a RETURNEDDATE field. Its not good database practice to store derived data like this but it might give you some ideas. VB is the best way to go though.