0

I am fairly new to Access and am building a customer orders DB as part of an assignment.

This question is similar to: Calculated Field Using Data From Another Table thing is, I don't want to achieve this by having a separate queries view. I want the result auto calculated in my table.

So I have two tables that are linked together with a relationship and PK/FK: OrderDetailTable and ProductTable

Product Table has Primary Key Product ID and OrderDetailTable has that as a foreign key

OrderDetailTable
OrderDetail ID Product ID Quantity Order Sub Total
ProductTable
Product ID Product Title Product Description Product Unit Price

I want Order Sub Total to auto-calculate based on the entered Product ID and Quantity.

SimpleMind
  • 29
  • 7

1 Answers1

0

On OrderDetailTable form, have a multi-column combobox for selecting product. Include product price as a column of combobox. Reference price column by its index to calculate OrderSubTotal. Column index begins with 0 so if price is in second column, its index is 1. Expression in a textbox:

=Quantity * cbxProduct.Column(1)

When building a report, include ProductTable in query and create a calculated field: Quantity * Price.

If you want to save price into OrderDetailTable, that will require code (macro or VBA) in some event(s), perhaps form BeforeUpdate. Me!OrderPrice = Me.cbxProduct.Column(1).

June7
  • 19,874
  • 8
  • 24
  • 34
  • Would I be able to do this without having an OrderDetailForm? I currently have an OrderTable and an Order Form, the Order Form has the OrderDetailTable as a subdatasheet. I do currently have the Product ID as a ComboBox in the table, with the ID, Title, and Price selected/displayed. – SimpleMind Feb 02 '21 at 11:34
  • There is not actually a combobox in table, that is a lookup field not a combobox control so there is no way to reference column by its index because that is property of a combobox control. – June7 Feb 02 '21 at 11:42
  • Advise not to build lookup field in table, build combobox on form. A form can be used as subdatasheet. – June7 Feb 02 '21 at 11:54
  • Ok, I was trying to avoid having another form but it seems like any workaround to that is bad design. Thank you for your answers and all the help! :) – SimpleMind Feb 02 '21 at 12:14