How to get a calculated Total from a textbox into a Table (1 Viewer)

[StanJx]

Hi,

I am new to access and databases. I have 2 tables with the respective fields:-

1) tblItemMaster - ItemCode(PK,Related), ItemDes, Product, Rate
2) tblQuotationDetail - ReferenceNo(PK), QuotationDate, ItemCode(Related), Qty, Total

I need to calculate the Total by multiplying Qty with Rate. I do not know how to calculate them as they are on two different tables. But at the moment I have added a Textbox on the form called txtTotal giving this Expression =[Rate]*[Qty] I get the calculation in the Textbox but I need the amount to be registered in to the table tblQuotationDetail, Total Field. Could somebody please give me a solution for this.

Thanks in advance,
Stan

 

[jzwp22]

Welcome to the forum!

In general, calculated values should not be stored in the table, only the values that go into making the calculation. If you put a calculated value in a table and you change one of the values that went into the calcuation the total is not automatically updated.

Using a form as you have done is one way to show the total. Another way is to use a query. You would bring in both table and join them by the key fields and add a calculated field that does the calculation.

 

[StanJx]

Welcome to the forum!

In general, calculated values should not be stored in the table, only the values that go into making the calculation. If you put a calculated value in a table and you change one of the values that went into the calcuation the total is not automatically updated.

Using a form as you have done is one way to show the total. Another way is to use a query. You would bring in both table and join them by the key fields and add a calculated field that does the calculation.

Hi,
Thanks for your reply, I am not that familiar with queries. But I think i know how get the calculation. but could you give me some information on how to get the perspective record of the query to show in the form.

Thanks in advance

 

[Pat Hartman]

This is one of those times where you want to store what appears to be (but isn't) duplicate data. The Rate field in tblItemMaster can change over time so you need to know its value when the tblQuotationDetail record was created. I would store the Rate in tblQuotationDetail rather than Total and I would calculate the total in queries when I needed it. The simplest way to copy Rate is to include the Rate column in the RowSource query used for the ItemCode combo in the Quotation Detail subform. Then in the AfterUpdate event of the combo (the user selects an Item) you copy the Rate column to the bound Rate field.

Me.txtRate = Me.cboItemCode.Column(2)

The columns of a combo are a zero-based array so .Column(2) refers to the THIRD column. If Rate isn't the third column (ItemCode, Desc, Rate), then adjust the column index as necessary.

In the query used to populate the Quote subform add a calculated column for the Total and bind a form field to it rather than calculating the value in the form. That way, Access will take care of updating the Total and you won't have to write code to do it.

Select ItemCode, Qty, Rate, Qty * Rate As Total
From tblQuotationDetail
Order By ItemCode;