code to look up data from another table

[bdm1]

I have a lending library database (access 2010 on Windows xp). I have another patron registration database. I can link the Transactions (book loans) table to the registration database. Now what I would like to do is when a patron registers into the library and an item is overdue, a message will popup informing the librarian that the item is over due. I don't know how to do this. The correct syntax on the form ON Current event
that will accomplish this. How to refer to the table to look up the field name. From Transaction table If DUE DATE is < DAte() and if CHECKED IN DATE is Null then msg Box " (Book Title) is overdue". Sounds simple but am new to coding so will appreciate any help. Thanks

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bdm1]

Before posting my sos, I had looked at and studied the dlookup function. But I must be doing something wrong, because I could not get it to work with the if statement. That is why I posted hoping for some further guidance. Thanks for the suggestion though. i will just have to keep trying....

 

[dhookom]

bdm1,
Please provide some actual table and field names. Some sample data to allow us determine data types etc would be helpful.

Duane
Hook'D on Access
MS Access MVP

 

[lionelhill]

I wouldn't use DLookUp, because you're actually looking up something quite complicated based on several fields, and you may have multiple hits. You've got a transaction table for loans that presumably includes the BookID, the ReaderID, DateOut, DateDue, and DateIn as a minimum. What you're looking for is any entry in that table that matches exactly ReaderID, where DateDue has passed and DateIn is null. There might be several possible examples.

The best thing (I think) would be to write a query for it. Execute the query whenever you're dealing with a reader, and look to see whether it returns an empty set (no overdue books) or whether there are some records (overdue). As a bonus, this allows you to answer your reader's next question: "Oh, I didn't know! What books have I got on loan?"

Incidentally, there are two ways to deal with queries programmatically. You can either write and save a query, and execute it from vba as desired, or you can build a query in vba, and use it to open a recordset. I always do the latter, but use Access's query design tool to create something close to my desired query, switch to SQL-view, copy and paste into vba, and modify there (to replace fixed values in my query by things that will be variables in vba).

 

[bdm1]

Thanks so much for your reply, lionelhill. I don’t have much experience in this type of coding so the methods you describe are intriguing but a bit over my head. Will do more research to better understand what you are advising. BTW, this is volunteer project so it is not as if my life depended on it. Would just like to present them with a workable solution.
This is the way I was envisioning things to happen. I link the TRANSACTION table from the BookLoan application into Registration application. The unique identifier for both is the MembershipID.
When member registration form opens at library: Match the MembershipID from the Transaction table.
If Due Date is past today’s and the Checked in Date is Null, then a message to pop up saying something like: [Title] was due on [Due Date].
The title is field name [ASSET] and is a LookUp field from the Asset Table: . SELECT [Assets].ID, Assets.[Title] FROM Assets;
The Checked Out to field is also a LookUp field from the Members Extended query: SELECT MembershipID, [Members Extended].[Member Name] FROM [Member Extended];
Hope this makes sense and I do very much appreciate your willingness to help….