After update event order (I think) issue

[maxxev]

Hi, I have a form with a sub form.
The subform allows (up to) 4 entries with only one variable (Score).
I want to total up the scores, divide it by the total number of scores recorded and then return the result to the parent form into a bound variable (Score).

All of this works great.... with the one acception that the result is from BEFORE the update not After the update (e.g. if the scores are 9,10,10,10 and then I change the 9 to a 10, you'd expect to see an average of 10, but instead the average is 9.75 (e.g. the average is being calculated from the values PRE-update, not after the update).

Here is my code:

Dim SubCount As Integer, SubSum As Integer, SubAverage As Double, SubScore As Double, Result As String
[COLOR=#73D216]'My attempt to get the variable that hold sum of scores to update before the below code takes effect (this doesn't appear to work).[/color]
Me.SubFormSum.Requery
DoEvents

[COLOR=#73D216]'counts the number of scores entered[/color]
SubCount = Form.Recordset.RecordCount 
SubSum = Me.SubFormSum.Value
[COLOR=#73D216]'below divides the 2 above variables[/color]
SubScore = SubSum / SubCount

[COLOR=#73D216]'enters the above result into the bound object on the parent form[/color]
[Forms]![SFLF_Benchmarking_Info].Score = SubScore
        
[COLOR=#73D216]'below changes another bound variable based on the above sum[/color]   
Result = ""
If SubScore <= 5.5 Then
Result = "RE-TEST"
Else
End If
    [Forms]![SFLF_Benchmarking_Info].Action = Result

If I run through the code using F8 it works perfectly, so I belive the issue is that the fields are being populated BEFORE the first line of functional code (this bit: Me.SubFormSum.Requery) is actually taking effect for some reason.

Much appreciated for any suggestions.

Cheers

 

[MajP]

me.dirty = false
will commit changes at the form level to the underlying recordset level.

 

[maxxev]

Hi, thank you for the suggestion but that has made no difference.

I have tested the code with a text box on the parent form with the following code:

=[Forms]![SFLF_Benchmarking_Info].[SFLF_Benchmarking_Attendee_Scores]![SubAverage][

This takes a calculated unbound value from the subform ("SubAverage") and displays it in the parent form, this value IS correct, however it is calculated milli seconds AFTER the "After update" VBA code completes . So the sums work, just not before the VBA fires.

Here is the current code:

Private Sub Score_AfterUpdate()
'DoCmd.SetWarnings False
Dim SubCount As Integer, SubSum As Integer, SubAverage As Double, SubScore As Double, Result As String

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
Me.Dirty = False

Me.SubFormCount.Requery 'Re-queries the sub form text box counting "Score"
Me.SubFormSum.Requery 'Re-queries the sub form text box Summing "Score"

Me.SubAverage.Requery 'Re-queries the sub form text box averaging the above 2 text boxes
'DoEvents 'is meant to ensure that the above events happen before the below (didn't work)


SubCount = Form.Recordset.RecordCount 'Counts records in subsheet
SubSum = Me.SubFormSum.Value 'Takes value from SubFormSum text box

SubScore = SubSum / SubCount 'Divides the above 2 variables by each other

    [Forms]![SFLF_Benchmarking_Info].Score = SubScore 'Makes parent form bound value = average(this equates wrong (before the update))
    [Forms]![SFLF_Benchmarking_Info].TempSum = SubScore 'Makes text box "Temp Sum" display average (this equates wrong (before the update))
    [Forms]![SFLF_Benchmarking_Info].TempScore = [Forms]![SFLF_Benchmarking_Info].[SFLF_Benchmarking_Attendee_Scores]![SubFormSum].Value 'Makes "tempscore" = sub form sum of score (this equates wrong (before the update))

    Result = ""
    If SubScore <= 5.5 Then
        Result = "RE-TEST"
        Else
    End If
    [Forms]![SFLF_Benchmarking_Info].Action = Result '(this equates wrong (before the update))

'DoCmd.SetWarnings True
End Sub

 

[maxxev]

I would like to know how to fix the above still, but i've decided at this point that it would be a lot easier to just use unbound text boxes on the parent forms that work through sums on the sub-forms, then on the on-close event of the form, make the bound values = the unbound values.

Cheers