Hi Forumy people, I have a quick question regarding VBA and Access.
The thing is, I'm creating a library database at work to keep track of a load of "stuff". The database contains 2 tables - one for the items, and one for details of loans. I've already knocked up necessary forms and everything for viewing and editing the database, the database tables are linked via item IDs, etc. However, I'd like to automate the part of the database which lists the number of items which are available. Or to elaborate, I have 5 of Item A, there have been 4 of them loaned out of which 2 have been returned, that leaves 3 available.
At the moment, the "how many are available" field needs to be manually updated. What I want to do is to create a function whereby every time the loan details for an item are modified, the details of the loans for that item are checked, a count is made of how many loans don't have "date returned" fields completed (ie, they're still out on loan) and this data is used to calculate how many items are actually available.
The problem is that my knowledge of VBA isn't brilliant and I'm a bit stuck as to how to do this. This includes which event trigger to use for the call, as well as how to go about pulling out specific records from the loan database without having to loop through the entire lot.
Anyone have any ideas at all? Or is more info needed?
Cheers in advance and all that!