How to update Parent form controls from the child / subform line totals (1 Viewer)

[nector]

I want to update my parent form controls from the results of the subform line totals so that people do not need to capture the results into the parent form where they are mandatory required manually.

I'm currently picking the totals from the subform into the parent form unbound controls by the use of the four codes below:

=[sfrmstockinDetails Subform].[Form]![txtTotalTaxable]
=[sfrmstockinDetails Subform].[Form]![txtTotalcounting]
=[sfrmstockinDetails Subform].[Form]![txtvattaxation]
=[sfrmstockinDetails Subform].[Form]![txtgrandTax]

Now to move the values from the unbound controls in the parent form shown above to bound controls so that they part of the parent form inputed data , I use the four codes

Public Sub txttotlaLinesDetal_Click()
Me.totItemCnt = Me.txtTotalFinalCouunt
Me.totTaxblAmt = Me.txttotlaLinesDetal
Me.totTaxAmt = Me.txtvattax
Me.totAmt = Me.txtvattax + Me.txttotlaLinesDetal
End Sub

But the above code has some disadvantages because sometimes users tend to forget to launch the code and so the data is not saved together with parent form , on the save button I tried to use the code below , but its failing.

Private Sub CmdSaveDetails_Click()
Call txttotlaLinesDetal_Click
DoCmd.Save
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmstocksin"
End Sub

Any idea how fix this problem

 

[Josef P.]

Any idea how fix this problem

Do not save any totals data in the main data set.
And if this is absolutely necessary (for whatever reason): you are using the SQL server, aren't you? Let it do this via a trigger.

 

[nector]

Do not save any totals data in the main data set.
And if this is absolutely necessary (for whatever reason): you are using the SQL server, aren't you? Let it do this via a trigger.

So in this case we have to be capturing manually because its a requirement for a Tax API otherwise it will give an error when sending data to the tax server

 

[Josef P.]

And the data source for the tax server must be the table, it cannot be a view that provides the data including the calculated total values?

I would then implement this using an insert/update trigger of the 1:n table and write the total values to the main table.
In the client, you then have to update the values in the current data record after leaving the subform, if they are available in the data source.

 

[Pat Hartman]

By making the source for the API a view as @Josef P. suggested, you solve the problem without having to violate any normalization rules and leave yourself open to data anomalies.

 

[gemma-the-husky]

Another way is not to try to use the subform to update the main form, but simply requery the main form.

I can see that there could be an issue that, for instance, an order total is slightly different to the sum of the order lines because of the effect of rounding, and sometimes the best solution is to violate "normalisation" by storing totals, especially where you need to be able to prove sales tax values, for instance.

 

[Pat Hartman]

I can see that there could be an issue that, for instance, an order total is slightly different to the sum of the order lines because of the effect of rounding, and sometimes the best solution is to violate "normalisation" by storing totals, especially where you need to be able to prove sales tax values, for instance.

That is a valid reason for the violation but it is safest to use triggers to do the updating to make sure the values are always current

 

[nector]

Another way is not to try to use the subform to update the main form, but simply requery the main form.

I can see that there could be an issue that, for instance, an order total is slightly different to the sum of the order lines because of the effect of rounding, and sometimes the best solution is to violate "normalisation" by storing totals, especially where you need to be able to prove sales tax values, for instance.

Thanks a lot gemma you got it right , however, I have sorted it out by ensuring that all the tables linked to the server have timestamp and moved the code that commit the late changes to the save button and then add a requery button as below to confirm the changes.

Private Sub CboEditStock_AfterUpdate()
On Error GoTo Err_Handler
Dim LTAudit As String
LTAudit = Nz(DLookup("Status", "tblstocksin", "StockInID =" & Me.CboEditStock))
Me.Filter = "StockInID  = " & Me!CboEditStock.Value & ""
If (LTAudit <> "") Then
    Beep
    MsgBox "This document is already approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Me.FilterOn = False
Else
    Me.FilterOn = True
End If
Dim Records As DAO.Recordset

    Set Records = Me![sfrmstockinDetails Subform].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
              
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
Exit_CboEditStock_AfterUpdate:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CboEditStock_AfterUpdate
End Sub

I think with MS Access you have to think outside the box most of the time, furthermore I enjoy being on this site. It makes me think critically and for sure through your variable answers, I treat all the responses as correct and then develop some ideas from there.