Is there any way to set up a field in an Access table that will automaticallyupdate from one or more fields in the same row. For instance; have a field called TOTAL get it's value from multiplying QUANTITY * PRICE?
This is not possible in a standard access table and isn't necessarily a great idea - it breaks one of the cardinal database normalisation rules which state that you shouldn't have a field in a table row that is calculated from one or more fields in that same row.
However, sometimes it is necessary to break the rules when performance is adversley affected but too many calculations. You can add the following code in the data entry edit Forms BeforeUpdate Event Procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
If IsNumeric(![QUANTITY]) And IsNumeric(![PRICE]) Then
![TOTAL] = ![QUANTITY] * ![PRICE]
Cancel = -1 'Record Update Failed
MsgBox "QUANTITY & PRICE required."
MsgBox Name & "_BeforeUpdate Error: " & Err.Number & ": " & Err.Description
It assumes that data entry in [QUANTITY] & [PRICE] fields is required before the record is valid and can be saved.
I would just like to say that's a really, really bad idea. Pull it when you need it, like ansentry says:
SELECT Quantity, Price, ([Quanity]*[Price]) As Total
You get the exact same information except it's garaunteed accurate as of that particular moment and you eliminate the possibility of inconsistency. As an added bonus, you also experience a performance boost when inserting/updating records because you don't have to perform an additional calculation.