I am looking for help with using DLookup on a form. I have a database with repeating records for each time a person rents out a piece of equipment. There are fields like Last_Name, First_Name, Sex, and Access_ID. There will be multiple records for each person as they rent out the same thing more than once over time.
I want the form to auto fill the fields of First_Name, Sex, and Access_ID after Last_Name has been updated. So basically I type in the last name, and Access looks up other previous records with the same last name, and takes the rest of the information from that past record and places it into the new record.
Any help? I have a feeling it is DLookup in the AfterUpdate field, but I don't know the code.
I guess in theory it's possible to do what you want, but it goes against the fundamentals of database design, namely data normalization. Just because you can do something doesn't mean you should Have a look at:
You don't have a separate database, just a separate table.
And to get what you're asking for is exactly what the first article outlines; you store the primary key of the person, and then use JOINS to get the rest of the information.
To illustrate what I'm trying to convey, take your example. What if there's a person named John Smith who rents a piece of equipment one day. Then a few weeks later he comes in and rents another piece, only this time, either by user-entry error or some other reason, he gets entered in as Jonathon Smith. Now the database would see these as two different people, even though they're really the same. The solution then is to only store the user's id in a transactions table (or however you have it set up), and store his name and such in a separate table to be pulled as needed.
If you read Paul Litwen's article (above), he does a great job of explaining it. If, however, you're still convinced that you want to do it with dlookups and that it's "just not worth all the work", well I can't stop you. But I'm not gonna help you hang yourself either