How to autofill a item price on access?

Question

So I've been making a database on access, and its been easy so far. Ive just made a table for the customer, but I've hit a snag.

I want it so that when I select my item from the lookup & relationship table, the price (a seperate column) gets filled in with that items price automatically. The price of the item is already in the same table as the item itself, but I can't get it to auto fill in. I'm kinda new to access, so please be easy with me!

Answer 1

Before you implement a solution, respond to DBG's comment as well as to my expanded comment here. It will do no good to implement a technically effective, but contextually inappropriate approach.

If you are redundantly storing the price as part of the transaction, do you do that to preserve the "PriceAsOfPurchaseDate" price as part of the transaction? Or, are you just copying the price because the table is set up that way? In the latter case, what I'm trying to say is that sometimes tables get set up with fields that duplicate fields in other tables out of lack of in-depth knowledge of good table design.

This is a tricky case, because, as I just noted, there is a scenario where price can justifiably be stored as part of transaction. In that context it reflects "PurchasePriceAsOfPurchaseDate", which may differ from prices charged to other customers or on different days.

The "ListPrice" is the one stored in the product table. It is the price from which discounts are calculated, for example. A good customer might get a 10% discount, so the PurchasePriceAsOfPurchaseDate represents the price minus that 10%.

Another customer on the same day might pay full ListPrice.

Also, over time ListPrice can change due to rising costs, or even to lowering costs.

If you don't record the PurchasePriceAsOfPurchaseDate you might have trouble getting accurate historical reports.

So, before we go off creating code to just routinely copy a duplicate value from one table to another, you need to be able to justify that duplication for legitimate business reasons.

Answer 2

Just FYI, it's normally recommended to avoid storing values you can look up from other tables. Prices may be an exception, but it depends on whether your prices change overtime and if you want to review historical price values for each product or item. Just my 2 cents...

Answer 3

Hi Grace B

Thank you for reaching out to the Microsoft Q&A Forum. 

Based on your description, it sounds like you're currently working directly in a table. In Microsoft Access, tables do not support automatically filling in values from another table in this way. To achieve this, you will need to use a form. 

After creating the form, you can refer to the suggestion from Q&A Assist to use a form and a lookup function like DLookup, as mentioned above. This allows you to automatically retrieve the price based on the item you select. 

Alternatively, if you're using a combo box, there is another simple method you can try: 

After creating the form: 

1.Open the form in Design View 

2.Select the field where you choose the item (for example, ID) 

3.Right-click and choose Change To > Combo Box 

4.Press F4 to open the Property Sheet 

5.Go to the Data tab and set Row Source to:  SELECT ID, Name, Price FROM Table1; 

6.Go to the Format tab: 

-Set Column Count = 3 

-Set Column Widths = 0cm;4cm;0cm 

7.Go to the Event tab >In After Update, > choose [Event Procedure] > then click ... 

8.Add the following code: 

Private Sub ID_AfterUpdate() 
    Me.Price = Me.ID.Column(2) 
End Sub 

This way, when you select an item, the price will be filled in automatically. 

I hope this helps. Please let me know if you need further assistance. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Use a lookup to choose the item, then use a calculated expression or a DLookup to show the price from the item table.

There are two common approaches:

1. Show the price only (do not store it again)
   Create a calculated field or control that pulls the price from the item table based on the selected item.
   • If this is in a form:
     1. Assume the form is bound to the customer/order table and has a combo box (lookup) called ItemID that points to the item table.
     2. Add a text box control for the price, called ItemPrice.
     3. In the form’s code module, use the AfterUpdate event of the lookup control to fill the price:

                Private Sub ItemID_AfterUpdate()
                    Dim strFilter As String
                    strFilter = "ItemID = " & Me!ItemID
                    Me!ItemPrice = DLookup("Price", "Items", strFilter)
                End Sub
        

        • Replace ItemID with the name of the key field in the item table.
        • Replace Price with the name of the price field in the item table.
        • Replace Items with the name of the item table.
   This uses DLookup in the same way as the example that looks up UnitPrice from a Products table when a product is selected.
2. Store a copy of the price in the customer/order table
   Use the same DLookup approach in the form’s AfterUpdate event of the lookup field, but bind the ItemPrice control to a field in the customer/order table. That way, when the record is saved, the price is stored. The core pattern is:

      Private Sub ItemID_AfterUpdate()
          Dim strFilter As String
          strFilter = "ItemID = " & Me!ItemID
          Me!ItemPrice = DLookup("Price", "Items", strFilter)
      End Sub
   

If working directly in a table (not recommended for this logic), Access cannot use a calculated field to pull values from another table; calculated fields in tables can only use fields from the same table. In that case, use a form with the DLookup method above.

---

References:

• Assign a control a value from a table
• Learn to build an expression
• Video: Create relationships with the Lookup Wizard
• Add a lookup or values list field to an Access web app
• Add or delete a column in a datasheet