Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Report Printing - Data not printing

    I have a Form I use to view and (hopefully) print a Report

    20110812 Account Statement Parameters 1.gif

    In the Report, in the Private Sub Report_Load() event an Opening Balance, Profit /Loss and Closing Balance is calculated and displayed when I View the Report, thus:

    20110812 Account Statement Parameters 2.gif

    20110812 Account Statement Parameters 3.gif

    However, when I click the “Print Report” button (created using the Command Button Wizard) the Opening Balance, Profit/Loss and Closing Balance are not displayed.

    As an example, the Opening Balance calculation process is:

    Me.Tbx_Opening_Balance = DSum("[Transaction_Amount]", "Tbl Trading Account", "[Transaction_Date] BETWEEN #01-Jan-2000# AND " & "#" & [Forms]![Fm Account Statement Parameters].[Tbx_Period_Start] & "# " & "AND [Broker_Name] = '" & [Forms]![Fm Account Statement Parameters].[Tbx_Broker_Name] & "' " & "AND NOT [Transaction_Type] = 'Paper Trade'")

    I’ve tried putting the above calculation in the Private Sub Report_Open() event, but that simply produces the error:

    20110812 Account Statement Parameters 4.gif

    referring to Me.Tbx_Opening-Balance, the Opening Balance textbox on the Report when I click “View Report” and prints (without the error message) a blank sheet on clicking “Print Report”.

    I can successfully print from the View Print display using CTRL+P, but I’d like to be able to use the Print Report button to go straight to my default printer. Any clues on how to overcome this issue will be appreciated.

    Regards

    BygAuldByrd

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Is the code behind the two buttons View Report and Print Report the same, except that one opens the Report in Print Preview Mode ("acPreview") and the other in Normal mode ("acNormal")?
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi John,

    The code under both buttons is identical except for the acViewNormal for printing and acViewPreview for on screen viewing. Code snippet as follows:

    Dim strReport As String

    strReport = Tbx_Report_Name

    DoCmd.OpenReport strReport, acViewPreview

    Regards

    BygAuldByrd
    Last edited by BygAuldByrd; 2011-08-15 at 19:37.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't know what the problem is.

    Does the code actually execute? To test this you can put in either Msgbox "runnning" or Debug.print "running"

    Try putting the code in the OnFormat event for the report section that contains the Controls.
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Thumbs up

    Hi John,

    I've done a little testing to see what actually runs when I View and Print my reports.

    When I View a report the following sequence occurs:

    Private Sub Report_Open(Cancel as Integer) runs,
    then
    Private Sub Report_Load() runs,
    then the Report is displayed.

    When I Print the report the sequence is:

    If printing to a PDF writer, a Save dialogue is opened requesting a filename, with a Save button,
    then
    Private Sub Report_Open(Cancel as Integer) runs,
    then
    the report gets printed.

    NOTE: that Private Sub Report_Load() does NOT run and this is the subroutine that had the missing amount balance calculations.

    You mentioned the OnFormat event in your latest reply, but that isn't a Form event. However I found that it is a ReportHeader event and including the calculations in the Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) subroutine triggered by the ReportHeader OnFormat event RESOLVED all the report issues I was suffering. It turns out that I didn't need any code in the Form OnOpen or OnLoad events, they were only needed in the ReportHeader OnFormat event.

    The events when running a Report now run in this sequence:

    Report viewing:

    Private Sub Report_Open(Cancel as Integer) runs,
    then
    Private Sub Report_Load() runs
    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [runs twice for some reason]
    then the Report is displayed.

    Report printing:

    If printing to a PDF writer, a Save dialogue is opened requesting a filename, with a Save button, otherwise this step is skipped
    then
    Private Sub Report_Open(Cancel as Integer) runs,
    then
    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) >[runs twice]
    then the Report is printed on either the printer specified in the Report design or the computer's default printer, depending on what has been set via the Print Preview Ribbon Page Setup icon.

    Many thanks once again for your kind and expert assistance. It has pointed me in the right direction yet again and proved of great value in my learning about coding MS Access VBA.

    Cheers

    BygAuldByrd

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you have it sorted.
    It is still not clear to me why the code worked when you use Preview Mode, but not with Printing. But I don't suppose that matters...as long as it works now.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •