Sum of Subform Values And Store In MainForm Field (1 Viewer)

[Ashfaque]

Hi,

I have main form (F_CrSalesInvoice) and subform ( SF_CrSalesInvoice_Footer) for invoicing. with InvNum in both forms related with one-to-many.

Other than many fields my main form contains a field called "Amount". When I save the invoice I want to sum total records from subform where invoice InvNum is equal from main form for each record in subform. The field in subform is called "ProjectCharge" this fields total value in that invoice I need to save in main form. I have following code that saves first time and update records too.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set rst = CurrentDb.OpenRecordset("Select * from T_CrSalesInvoice where Invnum=" & Forms!F_CrSalesInvoice!InvNum)
If rst.BOF And rst.EOF Then

rst.AddNew
rst!InvNum = Me.InvNum
rst!InvDate = Me.InvDate
rst!CustomerCode = Me.CustomerCode
rst!CustomerName = Me.CustomerName
rst!Amount = Me.Amount
rst!BankAccount = Me.BankAccount
rst!BankDetails = Me.BankDetails
rst!InvRemarks = Me.InvRemarks
rst!Mk = Me.Mk

    rst.Update
Else
Me.RecordLocks = False

rst.Edit

'rst!InvNum = Me.InvNum
rst!InvDate = Me.InvDate
rst!CustomerCode = Me.CustomerCode
rst!CustomerName = Me.CustomerName
rst!Amount = Me.Amount
rst!BankAccount = Me.BankAccount
rst!BankDetails = Me.BankDetails
rst!InvRemarks = Me.InvRemarks
rst!Mk = Me.Mk
       rst.Update
End If
rst.Close
Set rst = Nothing

All the fields updating in main table/form except Amount (a temp text field I created on form and place =[SF_CrSalesInvoice_Footer].[Form]![TotAmount] which displays total amount of Invoice). The sum of all the records in that subform I need to save in main tbl/form.
Do I need to create any update query and run in this vba code somewhere.

Can someone help please.

 

[bob fitz]

It's not usually considered to be best practice to store calculated values.
IMHO it might be better to have a hidden textbox in the footer section of your subform that does the calculation and then reference that field from a textbox on the main form. Very simple to do. Post a copy of the db if you need further assistance.

 

[Ashfaque]

Thanks Bob,

That I did already and showing me total value of subform onto the main form. But I need that total value to be stored in Main form's table in relevant invoice record.

This invoice value shall be adjusted in future somewhere.

 

[bob fitz]

Thanks Bob,

That I did already and showing me total value of subform onto the main form. But I need that total value to be stored in Main form's table in relevant invoice record.

As I said in my first post, that's not normally considered to be the best thing to do. Calculations should not usually be saved as data. Usually better to do the required calculation whenever and wherever it is required.

Why do you feel the need to save the calculation?

 

[Gasman]

Thanks Bob,

That I did already and showing me total value of subform onto the main form. But I need that total value to be stored in Main form's table in relevant invoice record.

This invoice value shall be adjusted in future somewhere.

Whilst not advisable, have the control on the mainform mapped to the field in the main table.
Then Update that control with the subfom total value.

However you have bigger problems than that, the total for those figures is 562.50 not what you show.?

 

[Ashfaque]

Why do you feel the need to save the calculation?

Bcz I will clear the invoice amount to 0 in "Payment Receipt Master " once received from client.

 

[Gasman]

You are meant to calculate both the invoice items and the payments and compare one against the other, not just change something to zero?

I could change that to zero as the customer is a friend of mine?
Of course I could also add a ghost payment showing that they had cleared the invoice, but that should be easier to track down?

 

[Ashfaque]

I did it in a very simple way in above code like this. These 3 lines I please at very beginning of code and it worked without further coding.

Dim X As Integer
X = Me.Amount
rst!Amount = X

And thats all.

May be there is another way with strong coding but this worked for me well. But still I am interested to know there is any other way

Thanks all of you.

 

[Gasman]

So that is not going to allow anything other than whole numbers?
Would not even hold the phantom amount you showed ?

Why have x at all ,just set the recordset field to the amount?

 

[Ashfaque]

Why have x at all ,just set the recordset field to the amount?

Actually when I execute the whole code thru save cmd btn, the sum amount on main from field for a fraction of second and default value of field Me.Amount which is set as zero (0) is saving in the main table.

Thats why I saved invoice value in a temp variable and then saved to tbl

 

[Gasman]

Actually when I execute the whole code thru save cmd btn, the sum amount on main from field for a fraction of second and default value of field Me.Amount which is set as zero (0) is saving in the main table.

Thats why I saved invoice value in a temp variable and then saved to tbl

But as an integer????????

Sub TestInteger()
Dim x As Integer

x = 625.5
Debug.Print x

End Sub

626

You really need to test your code