Save invoice Total in table (1 Viewer)

[buratti]

I have an invoice form with an invoice subform displaying the items and amounts (similar to the Northwind Orders form). I need to have the invoice total be saved in the underlying invoice table once a button is clicked (which when clicked, does other things also). I kinda got it, but just having some trouble.

What I did is in the subform's footer I entered a unbound field that calculates the total of the amounts using the sum() function. Then in the onClick of my control I entered the code:

Me.invoice_Total = [InvoiceSubform].Form![SumAmount] (sumAmount being the unbound control I was talking about befor and invoice_Total is bound to the Invoice_Total field in the Invoice Table)

I have two problems with this. First, that line is one of the first lines in the OnClick procedure, and for whatever reason that line completes before that actual calculation of the invoice total completes, so it is always being set to 0 (or the previous value if any amounts are being edited).

My second problem is that the Invoice_Total field is leaving out the decimal. So if the invoice total calculated in the subform is $45.67, the Invoice_Total field will only display 45. In the Invoice Table I have the data type set to currency.

What am i missing here???

 

[Simon_MT]

I use two queries:

One to set the total to zero

The second to total in my case [Client Sale Amount]

[Client Sale Amount]+CLng(([Orig Sale Foreign Amt]/[Client Sale Foreign Rate])*100)/100

Please note you are adding [Client Sale Amount] to itself - i.e. Totalling.

A query with totals could not update.

Simon

 

[joemach]

Answered in line ...

I have an invoice form with an invoice subform displaying the items and amounts (similar to the Northwind Orders form). I need to have the invoice total be saved in the underlying invoice table once a button is clicked (which when clicked, does other things also). I kinda got it, but just having some trouble.
Why do you want to save the invoice total in a table? That is bad practice.

What I did is in the subform's footer I entered a unbound field that calculates the total of the amounts using the sum() function. Then in the onClick of my control I entered the code:

Me.invoice_Total = [InvoiceSubform].Form![SumAmount] (sumAmount being the unbound control I was talking about befor and invoice_Total is bound to the Invoice_Total field in the Invoice Table)

I have two problems with this. First, that line is one of the first lines in the OnClick procedure, and for whatever reason that line completes before that actual calculation of the invoice total completes, so it is always being set to 0 (or the previous value if any amounts are being edited).
Rather than using a command button you should use a control source for the unbound control. So in the control source for Me.Invoice_Total you would have:
= [InvoiceSubform].Form![SumAmount] then when you left the last control that you enter data for the calculation enter Me.Recalc

My second problem is that the Invoice_Total field is leaving out the decimal. So if the invoice total calculated in the subform is $45.67, the Invoice_Total field will only display 45. In the Invoice Table I have the data type set to currency.

What am i missing here???

How many decimal places are you telling Access to give you?

 

[Simon_MT]

I will tell you why, there are times when it is more efficient.

Simon