I have a small lending library database (100members, 70 books) and I'm trying (hard) to put a routine in at the point of lending entry to double check that the book isn't already "out on loan" in the database.
The loan table has 400+ entries with 5 fields containing 3 integers and 2 dates; loan ID, Member no., Title no., Month out and Month back. Loans are made on one day each month so there there no real date issues. The month back field is empty until the book is returned.
At the point of data entry, on my form (via list box) I need to check the title no. against a query on the loan table which returns a list of about 20 titles on loan where the month back field is empty.
It should be simple, if the title no. isn't on that list the entry is good, if it is on the list, there is a mistake somewhere.
I thought I probably needed some vba code that uses Dlookup in a function which I can call after update in my dropdown list but just prior to saving the record.
I've had a number of attempts at writing the code but failed.
I'm assuming that something like the following is needed
Dlookup ("[ID]","DVDs_on_loan", _
"[Loan_Title_no] = Form![Newtitleloan]")
For simplicity [Newtitleloan] is a form variable that holds the book no.
In theory it will return the ID of an earlier loan if the current title no. ( as yet usaved) appears in my query. However I a get a compile error "Expected: =
I just can't see where I'm going wrong.
Any help very welcome
Pete