Use VBA to store a calculated value?

[Andrew Fergus]

Hi everyone

After a few days of pulling my hair out, I am in a bit of a bind and need some help with VBA & forms.

I want to store a value calculated on a form but am having trouble getting VBA to store the correct value. I know I shouldn't store a calculated value but it will make subsequent tax calculations, queries, forms & reports so much simpler if the 'nett value' of the invoice was stored. I'm using Access 2000.

The 'nett value' is the sum of the 'extended values' of the line items on an invoice and this is the value I want to store. One invoice can have many line items - the 'extended value' is the product of the quantity and price. I want to store this sum of the extended values for each invoice. This should be easy but I must be missing something really simple.

I have a main form 'frmInvoices' for the invoice header info (e.g. customer, date, invoice number, nett value etc) and a subform 'sfrmInvoiceItems' for the line items. The forms are based on a query that pulls together all of the data from the underlying tables. Within the subform I have calculated the 'nett' value by using
=Sum([Item_Ext_Value])
in the subform footer. This works fine.

How do I then write this calculated value from the subform footer into a bound text box in the main form? If I use something like
=[sfrmInvoiceItems].[Form]![InvItemsSubTotal]
as the control source in an unbound text box the value isn't stored.

I have tried the following VBA script in the after update events of the price and quantity fields within the subform to try and store the subtotal in the field 'Nett_Value' on the main form :

Private Sub Item_Price_AfterUpdate()
    Me.Item_Price = Int((100 * Me.Item_Price) + 0.5) / 100
    'forces the price to 2 dp
    
    Me.Item_Ext_Value = Int((100 * Nz(Me.Item_Quantity) * Nz(Me.Item_Price)) + 0.5) / 100
    'rounds up by a maximum of half a cent
    
    Forms![frmInvoices].[Nett_Value] = Me.[InvItemsSubTotal]
    'store the subtotal calculated in the subform footer??
End Sub

Private Sub Item_Quantity_AfterUpdate()
    Me.Item_Ext_Value = Int((100 * Nz(Me.Item_Quantity) * Nz(Me.Item_Price)) + 0.5) / 100
    Forms![frmInvoices].[Nett_Value] = Me.[InvItemsSubTotal]
End Sub

but it is always 1 line behind. If I add a new line to an invoice, the nett value won't show, but when I add a 2nd line item, the total stored after I add the 2nd item is the subtotal from just the 1st line. If I then add a 3rd line then the subtotal is for the first 2 lines only. The stored value is always 1 line behind the calculated value.

What is going wrong with this? I have tried using a DSum function to set the value using VBA but I have the exact same issue of the stored subtotal being 1 line item behind the actual subtotal. Is there a delay in writing the subtotal in the subform footer or the record in the table such that the VBA script is finished before the form / table is updated? I wouldn't have thought so given I am using the after update event. Or is there something wrong with my logic / process?

Help!

Andrew

 

[meloncolly]

A different approach would be to run an update query in the onclose event of the form, updating the field "nett value" in your main table and setting it equal to the sum of the extended prices for all related invoice line items and only do this where "nett value" is currently NULL. In this way, you can be sure that the user has COMPLETELY finished the invoice.

 

[Andrew Fergus]

Hi

Thanks for your reply.

Last night, after surfing the 'net looking for a solution, I had thought about the same answer. I figured I could display calculated figures on the form but once either the form is closed or the invoice is printed, then a query would update the values back in the table.

The issue then becomes a process issue to prevent out-of-sync data.

Thanks for pointing me in the right direction!

Andrew