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!

SELECT Count([Loan ID]) AS Loans FROM [Loan DATA] WHERE IsNull([Date Loaned Out]) = FALSE AND IsNull([Date of RETURN]) = TRUE GROUP BY [Test ID]

Thanks very much for that. I'll have a play with it at work tomorrow to see if I can get it (with any necessary changes) to work.

Muchly appreciated!

SELECT [Test ID], Count([Loan ID]) AS Loans
FROM [Loan DATA]
WHERE [Date Loaned Out] IS NOT NULL
AND [Date of RETURN] IS NULL
GROUP BY [Test ID]

Cheers, that pretty much works for creating a query which lists the number of current loans. The next problem is how to make use of that data. Access won't let me use the "Loans" field from the query in my form because the data for that query comes from one of the tables being referenced in the form. Not a big deal I'd say, but Access disagrees.

Gah I hate Access some times >:-(

Thanks for the help so far mind you!

UPDATE Tests INNER JOIN [Loan DATA] ON Tests.[Test ID]=[Loan DATA].[Test ID] 
SET Tests.[Available Copies] = Tests.[No of Copies]-(SELECT Count([Loan DATA].[Loan ID]) AS Loans 
     FROM Tests INNER JOIN [Loan DATA] ON Tests.[Test ID] = [Loan DATA].[Test ID] 
     WHERE ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL)));

SELECT [Loan DATA].[Test ID], Count([Loan DATA].[Loan ID]) AS Loans
FROM Tests INNER JOIN [Loan DATA] ON Tests.[Test ID] = [Loan DATA].[Test ID]
WHERE ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL))
GROUP BY [Loan DATA].[Test ID];

UPDATE Tests
 
FROM [Loan DATA] 
 
SET [Available Copies] = Tests.[No of Copies]-(SELECT Count([Loan DATA].[Loan ID])
 
WHERE Tests.[Test ID] = [Loan DATA].[Test ID]
AND  ((([Loan DATA].[Loaned TO]) IS NOT NULL) AND (([Loan DATA].[Date of RETURN]) IS NULL)))
;

Access added those so I assume they're allowed!

Thanks for the new code. Unfortunately you can't use a "FROM" statement with an "UPDATE". :(

Regrettably not unfortunately. Which is a pity because Access has really been annoying me today due to its extreme shitness. If the worst comes to the worst, I'll just have to update 2 fields every time a loan comes or goes. Hardly an ideal solution though.

Might have another go at it tomorrow. Might be possible to find some way of doing it by modifying the table design a little perhaps.

Solved!

OK it's a bit of a bodge and one fuck of a work-around, but it now works.

The final solution involves 4(!!) queries and a temporary table. The first query empties the temporary table, the second query fills it with a list of current loan counts (you can append whilst using "Count" in a sub query, but not update - bizarre!), the third query resets the "Amount available" field in the main "Tests" table to match the number of copies (needed so that returned items were being properly updated) and the fourth query takes the data from the temporary table, calculates the amount available from the total number of tests and the amount out on loan, then stuffs it into the main "Tests" table.

Bung in a couple of action events to execute that lot whenever the loan information is updated and the end result is that the form/table correctly updates itself whenever loan information is changed. One fuck of a bodge admittedly, but the part I'm bothered about is that it works. Plus this isn't going to be a heavily used database so overhead won't be a problem. Most of the time it's just going to be viewed and in which case, no queries are executed at all.

So. Thanks very much for the suggestions, help, etc - particulaly with the second query which lends most of its code from Ally and BP. And I now fucking hate Access (not that I exactly loved it before mind you)!