Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Auto Fill Form Help!

    Hello,

    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.

    Thanks!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    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:

    · Fundamentals of Relational Database Design
    · Rules of Data Normalization

    You'll probably want to have a separate table for people.
    Me.Geek = True

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    Okay, so lets say I had a database for each person that ever rented a piece of equipment, and had a separate database for daily use of the equipment. How could I accomplish what I am looking for.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    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
    Last edited by nckdryr; 05-06-09 at 19:42.
    Me.Geek = True

  5. #5
    Join Date
    Apr 2009
    Posts
    5
    Problem solved, thanks Nick! I just wanted to make sure it could be done the other way without having to read the lengthy article and spend the hours doing it.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •