Hi there

For a college project I'm modelling some functions (in a basic way) of a library loans desk within MS Access 2007.

I have created two tables - one of which holds information about items to be loaned, named stock, and another which holds details of library members.

I have written a SQL UPDATE query that updates both tables to reflect date item was borrowed and is due back, who has borrowed the book, amend available amount in stock and amend the amount library member can borrow.

My Question is this: In this SQL, I would like to output an error message to the screen (rather than an alert box saying "0 rows updated" etc) if any of the following occurs:

- Attempt to borrow a book that is reseverved (Reserved status set as a Yes / No field)
- Attempt to borrow a book where there is no stock available
- Attempt to borrow a book where the user can not nborrow any more books as he / she has exceeded their amount of items borrowed limit

Here is the SQL as it currently stands:

UPDATE Members AS M, Stock AS S

SET S.Available = No,
S.ID = [Enter Borrower ID Code],
M.Items = M.Items-1,
S.LoanDate = now(),
S.DueDate = now()+s.Loan_type

WHERE M.ID=[Enter Borrower ID Code]

AND S.CATref=[Enter Item Reference Code]

AND S.Reserved=No

AND M.Items>0

AND S.Available=Yes;