I have a data entry form similar to an invoice entry screen with header and line items however my header details identify a set of items being tested (such as a serial/lot number of goods and their manufacture date) and the line items refer to individual samples from this group. Each sample is measured for specific criteria and various calculations are made. The difference with this scenario (vs an invoice) is that the sample details (line items) are saved as each is processed. If there 20 samples in the group, there are 20 separate saves performed.
When each sample is saved, I (re)calculate summary information (number, average weight, total weight) for those that "Pass", "Fail", and the aggregate "Total". One of these calculations provides Total Average Weight for the group.
MY PROBLEM IS that with each new sample, I have to loop through, recalculate, and update a field in all of the previously saved line items. I've tried doing this with VBA code but it only updates the first record (rather than ALL records that share its ReportID). I also tried doing an update query but it skips the first record in the group and all the other updates for the field in question are wrong.
I want to update the "PercentDiff" field for all samples belonging to the current ReportID. The equation for this is:
((LineItems.SampleWeight / Form.TotalAverageWeight) -1)*100
Note: TotalAverageWeight is updated to both the report header AND the active form prior to looping through the details to calculate each sample's new "PercentDiff".
Below is the code I'm using:
[Public Sub RecalcPercentDiff()
Dim RS As Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset("SELECT * FROM LineDetails WHERE Filename = '" & Me.txtReportID & "'")
RS.MoveLast
With RS
.Edit
!PercentDiff = ((!SampleWeight / Me.txtTotalAverageWeight) - 1) * 100
!TOL = IIf(Abs(!PercentDiff) > 3, "Fail", "")
RS.Update
End With
RS.Close
Set RS = Nothing
End Sub]
I attached an Excel file showing manual calculations of what's expected as well as screenshots of the line item datasheet with what the program is providing, as well as the results of the QueryUpdate (no change in numbers from code calculation).