Calculating field from look up table

[hmcquade]

Hi there,

I have a form showing printing information. I have a field called 'Quantity', one called 'Papersize' and one called 'PrintCost'. The PaperSize field is a combo, that takes its values from another table called PrintCosts. This table shows different types of paper size, with a corresponding 'Cost' field showing price per sheet. After the papersize has been chosen and quantity has been updated, i would like the PrintCost field to automatically be updated by taking the papersize and multiplying it by the quantity and come up with a total print cost by looking at the PrintCost table. I have tried this in the Quantity afterupdate event:

Dim dblCost As Double
dblCost = Me.Quantity * Nz(DLookup("[Cost]", "PrintCosts", "[PaperSize] = '" & Me.PrintSize & "'"), 0)
Me.PrintCost.Value = dblCost

I am getting: "Run-Time error 2001: you cancelled the previous operation"

Any ideas?

[jzwp11]

Welcome to the forum!

First, you would use different code, and second, you should not store the calculated cost (PrintCost) in the table.

You would, however, need to store the price, quantity and paper size (or a foreign key that refers to it). I am assuming that your prices may change but that you would want to store the price at the time you create the record.

On your form, you would have bound controls for quantity (texbox), paper size (combo box) and price and an unbound control for the cost.

For the combo box, set the row source such that it includes the price in addition to the other fields you want. In the after update event of the combo box, populate the price control with the column of the combo box that holds the value.

For example if the combo box's row source was as follows, you can reference the cost field.

SELECT papersizeID, txtPaperSize, cost FROM PrintCosts

The code in the after update event of the combo box would be like this:

me.costtextboxcontrolname=me.comboboxname.column(2 )

Note Access counts the columns starting at 0 not 1.

You would do a simple calculated control for the printcost, but you would not store it since you have all the key information stored that is necessary to calculate it when you need it. (Storing the calculated cost can cause issues with your data integrity if one of the key fields that was originally used in the calculation is changed and the total cost not updated accordingly. For this reason, it is best not to even store it).