Tracking Items Lent and Returned or Outstanding

[TheToeOfPooh]

It has been a while since I did any work with Access so any help will be very much appreciated.

I have been asked to produce a simple database to track the issues and returns of manuals and books (ISDN Nos are not used).

I have three tables
tblBooks
Title, Author,Ref

tblRecipients
Surname, Firstname,Function,Job Title and other associated fields

tblLoanAndRecipients
This will include most field from tblRecipients,
the Title field from tblBooks - from a query I have created against tblBooks
plus
4 extra data entry fields comprising
DateIssued Default value is Now()
DateDue When the loan period has expired, can't be blank
DateReturned When item recieved back into the library
A Memo field For Notes

The problem I have
I would like the user to be able to select a Surname, FirstName and Location using DropDown options (using Lookups to either the tblRecipients or related query), and then the rest of the values to be automatically displayed if there is a match found.

I will then aim to create a query - to drive a report - on books outstanding
e.g.
If
field DateReturned is Blank
and
field DateDue is older than Today()
Then
List selected details including any notes.

Can anyone help me to automate the lookups or sugest a more efficient way of accomplishing this task?

Thank you in advance.

Bernard

 

[TheToeOfPooh]

After some debate the user's expectations have been modified to accept drop down options for each field.

I have used a series of queries to allow selection of relevant data from the source tables and applied the Unique Value only option within these queries.

Once I had appied a Is Nul criteria
to select all records where the Date Returned field is empty
the whole application started to make sense.

So please view this question as Closed unless anyone has any helpful tips they wish to add.

Bernard