Results 1 to 10 of 10
  1. #1
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6

    Question Data changes from Report View to Print Preview

    Hi All,



    I've been scouring the depths of forums for the last 2 days trying to see if anyone else has come across this issue!

    I have a report with 4 sub reports and I am trying to add up values from two of the sub reports into the main report as a final fee.

    The two sub reports both have a running total for their respective sub reports, and I am trying to add the final figures together for a final fee.

    When viewing the report in Report View, this works fine. When I open this in print preview mode, the figures change, taking the first value from each sub report not the final running total.

    I realised this using some VBA code and having a watch window.

    Please advise!

    Thanks,

    Sarith

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Each sub-report has a total field in its footer.
    the main report can just add them: sub1.txtbox+sub2.txtbox

    use the builder to get the path correct.
    or
    make a subreport that uses a query that adds the sub queries.

  3. #3
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6
    Hi ranman256,

    Thank you for this. I've done that, but the totals don't seem to be right when I've viewing in print preview. In print preview they only consider the first record in the sub report rather than the total for the sub report.

    Happy to try and recreate the subreports to make sure it's not user error!

    Thanks,

    Sarith

  4. #4
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6
    Hi ranman,

    I did as you suggested, but this didn't work as my queries had calculated fields within them.
    I have managed to find a work around for this using some VBA code to perform the calculations I wanted in the final report.
    My new issue is creating a loop for these reports, updating each report with its calculated figure - so far it is only showing the value from the first record calculation in each report.

    This is my code:

    Code:
     Private Sub Report_Load()
    For i = 1 To Recordset("tblAssociates").RecordCount
    If rptAssociateRolesSpecSub.Report!txtrevisercount = 0 Then
        'Debug.Print "No Revisers"
        Me!txtFinalFee = rptComponentFeesByRolesSub.Report!txtItemWriteFee + rptComponentFeesByRolesSub.Report!txtReviserCompFee + rptComponentFeesByRolesSub.Report!txtScrutFee + rptComponentFeesByRolesSub.Report!txtLAWFee
        'Debug.Print rptComponentFeesByRolesSub.Report!txtItemWriteFee + rptComponentFeesByRolesSub.Report!txtReviserCompFee + rptComponentFeesByRolesSub.Report!txtScrutFee + rptComponentFeesByRolesSub.Report!txtLAWFee
    Else
        'Debug.Print rptAssociateRolesSpecSub.Report!txtrevisercount
        Me!txtFinalFee = rptComponentFeesByRolesSub.Report!txtItemWriteFee + rptComponentFeesByRolesSub.Report!txtReviserCompFee + rptComponentFeesByRolesSub.Report!txtScrutFee + rptComponentFeesByRolesSub.Report!txtLAWFee + rptSpecReviserFeesSub.Report!txtSpecRevisionFee
        'Debug.Print rptComponentFeesByRolesSub.Report!txtItemWriteFee + rptComponentFeesByRolesSub.Report!txtReviserCompFee + rptComponentFeesByRolesSub.Report!txtScrutFee + rptComponentFeesByRolesSub.Report!txtLAWFee + rptSpecReviserFeesSub.Report!txtSpecRevisionFee
    End If
    MoveNext
    Next i
    End Sub
    Thanks,
    Sarith

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    VBA should not be required for this, either the adding as Ranman suggested or creating a totals query and brining it in to your record source should be sufficient. Post your database with some data so we can see what is happening.

  6. #6
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6
    Phase 3 QP Fees v5.7.zip

    I've attached the database here. Its is a bit of a mess at the moment, containing ideas as well as potential final outputs.

    The report I'm interested in is the rptAssociateMain on, with sub reports rptAssociateRolesCompSub, rptAssociateRolesSpecSub, rptComponentFeesbyRolesSub and rptSpecReviserFeesSub.

    The last two sub reports are fed by queries with the same name.

    I'm trying to create a grand total by associate on the main report, tallying up the fees from the queries and more depending what additional responsibilities the associates take (all in tables and linked forms.)

    Thank you!

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query containing AssociatePin and total fees, one record for each associate. Then join that query into your record source for the main report and use that field for your total. Altho it looks as tho your main report only contains the associate so the record source for the main report need only contain those three fields.

  8. #8
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6
    How would I do that?

    Sorry - only been using Access for a few days, usually an Excel geek!

    Would I need to run a query using Associate from tblAssociates and fees from previous queries?

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I'm trying to create a grand total by associate on the main report, tallying up the fees
    Create a new query, bring in tblAssociates. Now bring in all other tables/queries which contain the exact figure you are looking for in order to get the grand total. Join them to tblAssociates using a left join (right-click on the join line, under properties select all records from tblAssociates - this will prevent loss of data in case a record doesn't exist in one or more of the tables/queries).

    Add fields AssociatePin and name from tblAssociates. Create a calculated field in the query, Nz(thisfield) + Nz(thatfield) + ...

    Get that working.

    Then, in query design, click on the totals icon (you'll recognize it from Excel). This adds a new totals line at the bottom. Select Group By for associates and Sum for the calculation.

  10. #10
    AccessPadawan86 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    6
    Thank you very much! That's working perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-28-2017, 09:51 AM
  2. Switch from Print Preview to Report View
    By MatthewR in forum Reports
    Replies: 5
    Last Post: 10-20-2015, 12:40 PM
  3. convert report view to print preview
    By DarrenUD in forum Reports
    Replies: 3
    Last Post: 03-19-2013, 12:21 AM
  4. Replies: 1
    Last Post: 02-23-2013, 12:30 AM
  5. Replies: 10
    Last Post: 07-25-2011, 12:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums