• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

MS Access report problem with control/function refresh


I have an Access report with a sub report.  In the main report there is a text box (A) which takes a value from a control on the sub report.  The value in text box A is then used as one element of a grand total sum in another text box (B) on the main form.

Because the value of the control on the sub report could be null, as the sub form may not return any records, I use the function below as the Control Source for text box A to force a zero (this was suggested as the answer to one of my previous questions).

All this works fine if the report has a single record.  However where there is more than one record the Text box A value on the first record doesn't refresh for any subsequent record but stays the same on every page.  I have tried various requeries but no luck.

Hope this makes sense - any ideas?


text box A control source  =funcAvoidErrorProductTotal([chdReport].[Report]![txtTotal],0)

Function funcAvoidErrorFixedTotal(varFixedTotal As Variant, varReplaceWith As Variant)

     On Error GoTo funcAvoidError_Error
     If PBcurFixTotal > 0 Then
        funcAvoidErrorFixedTotal = PBcurFixTotal
        funcAvoidErrorFixedTotal = Nz(varFixedTotal, varReplaceWith)
        If funcAvoidErrorFixedTotal > 0 Then
            PBcurFixTotal = funcAvoidErrorFixedTotal
        End If
    End If

      Exit Function

      funcAvoidErrorFixedTotal = varReplaceWith
      Resume funcAvoidError_Exit

End Function
1 Solution
Reproduce the issue in a sample database and upload.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

I use a different approved for reports like this. I create a totaling query that sums the same records as in the sun report(s). I this add this totaling query to the query for the main/parant report.  Use NZ() to handle when the sum() field is null when using the total in a calculation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now