In Access, how can I pull data from another table by matching a field?

Question

I am working on an inventory system for a tool rental shop. I have two tables: Details and Tracking. Details has all the basic information about all the customers (Name, Barcode, ID etc.). Tracking is meant to create a log of rental activity.

Here's what I am trying to do: I am trying to scan a barcode and input the barcode to a form based off the Tracking table. However, once the barcode field has been filled in, I want other fields like Name, ID etc. corresponding to the entered barcode to be pulled up from the Details table and filled automatically in the form.

I was thinking of some expression like Name = Barcode.[Details]!Name and so on, but it does not work. Can anyone help me out with this? Thanks a lot.

JJ

Answer 1

You will need at least 3 tables to capture the tool rental information. You'll need a Customers table (which you have called Details), a tool table identifying each tool for rental, and a many-to-many table that captures tool and customer information such as which tool was rented by whom and when (each customer can rent many tools and each tool can be rented by many customers though not all at the same time).

As to how to populate related fields with information based on a barcode; there are a couple of ways this can be accomplished. You can use the After Update event of the barcode field to update the related fields using either a recordset or DLookup based on the barcode ID. If the barcode field is a combo box (to allow the user to more easily enter a number manually), you can store the extra information in the row source of the combo box and use the After Update event of the field to populate the other fields.