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.
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.
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.
Code:
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.