Different result when printing after previewing a report

I have a report that I start from a cmd button in preview mode. If I then print the report, I may get different results from what the preview screen showed.

The report runs some vb code started from the detail on format and on print events. There are also sub-reports that do the same. The code that gets called from these events, sets the visible property to false for controls that are empty (code courtesy of Steveb and 1William).

Public Function ControlDisplay(rpt As Access.Report)
Dim ctl As Control
Dim stTest As String, stTest1 As String
    On Error Resume Next 'not all controls will have datasource (line for example)
    For Each ctl In rpt.Section(acDetail).Controls
        ctl.Visible = (Len(ctl.Value & vbNullString) > 0)
    Next
End Function

It seems that if, on a sub-report, there are 2 records and the first record has all controls populated but the 2nd record doesn't have all controls populated, that the all the controls/labels get printed anyway. It's wierd because the first time you preview then print, the empty controls print, but if you preview/print again, it's ok.

Any ideas?

ps, I did a work around, and have separate print and preview command buttons, which seems to be working ok.
avoorheisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

heer2351Commented:
You would only have to call the above code from the onFormat event of you report. No need to call it again from the onPrint event.

Could you post the code of the onFormat event so I can have a look?
avoorheisAuthor Commented:
It's the same code that's called from both the on format and on print.

At first, I just had the on format calling the code. And did a print preview, all looked ok. But then when trying to print (preview still displayed, using print icon in toolbar), it didn't print right. If printed a second time, it printed ok.

I tried it again with only using the on format event and if I use
DoCmd.OpenReport stDocName, acViewNormal (instead of acPreview) it prints ok, but it still does the same thing if I use acPreview (preview looks ok, but the first pass print is not ok, using the print icon in the toolbar).

Hope I'm being clear. This only happens when when the subform has 2 records and some of the fields on the second record are empty (so should not display). I'll try an example of the report printout:

this is how it should look

          SAMPLE GOOD REPORT
Date:            1/1/03
Order#:        12345
Mortgage #:  1                    (start of subreport)
Grantor:        John Doe
Date:            1/1/03
Assigned To:  B of A
Mortgage #:   2
Grantor:         Sam Smith
Date:             2/1/03

         SAMPLE WHEN FIELD PRINTS WITH NO DATA
Date:            1/1/03
Order#:        12345
Mortgage #:  1
Grantor:        John Doe
Date:            1/1/03
Assigned To:  B of A
Mortgage #:   2
Grantor:         Sam Smith
Date:             2/1/03
Assigned To:

The "Assigned To:" should not show up because it didn't have data.

heer2351Commented:
To help you I will need to look at the code, could you please post this.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

avoorheisAuthor Commented:
The on format event for the main report detail and the on format for each sub report detail calls:

Public Function ControlDisplay(rpt As Access.Report)
Dim ctl As Control
    On Error Resume Next 'not all controls will have datasource (line for example)
    For Each ctl In rpt.Section(acDetail).Controls
        ctl.Visible = (Len(ctl.Value & vbNullString) > 0)
    Next
End Function
heer2351Commented:
Hmm I am not able to reproduce your problem.

Let's check if the code is called when doing a print after doing a preview, place a breakpoint in the ControlDisplay function on the on error resume next line. Now preview the report you should stop in the code, hit F5 to continue running. Then click on print, are you again taken to the code?
avoorheisAuthor Commented:
I was doing that the other day and just did it again. When I print, it doesn't stop at the breakpoint. But, I put a beep right after the line where the break is. I hear the beep when I print (so it's like it goes throught the code) but it doesn't stop at the break.

When I do the preview, it stops 6 times (and beeps 6 x) at the break, but when I print there's only one beep (but doesn't stop). Also, I do the preview, I see it looks format correctly, then print and it's not formated correctly. If I print, right away, again, it's formated correctly. ????????????

(there are 4 subreports, so I expect more than 1 stop, but 6 doesn't make sense either
heer2351Commented:
Hmmm very strange, would you mind sending a zipped version of your database to me so I can have a look?

heer2351 at hotmail.com
heer2351Commented:
Alan I had a look at your database. The reason for the onPrint event not being fired is that you did not set the event handler, i.e. open the report in design view and have a look at the events tab you will see that the onPrint is empy. You probably copied the onFormat event and renamed it, access does not like that. If you cut and copy the already renamed event access will set the event handler. But like I said before it is not required.

I have not yet solved your problem, but I am thinking that the problem is due to the fact that the subreport is part of a footer instead of the detail section.
avoorheisAuthor Commented:
Do you think, as a general rule, it would be better to use the main reports, footer onformat event instead of the subreports, detail onformat event?

As far as the onPrint event, I was going back and forth with using/not using the onPrint event, so the code was there and I just disabled it by deleting the Event Procedure. Since onPrint event is not needed, does Access actually run through the code a second time if you print while previewing the document?
heer2351Commented:
I think I have found the solution to your problem. Change in your onFormat event the line:

ControlDisplay Reports("reportName")
or ControlDisplay report_subreportName

with
  ControlDisplay Me

That solved the problem for me :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
avoorheisAuthor Commented:
Thanks!
Do you have any idea why?
I haven't used Me by itself before and couldn't find anything in the help files. Do you know of any documentation about it?

thanks again
heer2351Commented:
My guess is that when then function is called for the second record on the subreport time access created a new instance of your report that has a different name as the first report instance. Because you only refer to this first report all records will have the same controls made visible as the first record.
By using Me you are sure that you are refering to the correct instance of the report.

From the help:

<quote>
The Me keyword behaves like an implicitly declared variable. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module. For example, suppose you have the following procedure in a module:

Sub ChangeFormColor(FormName As Form)
      FormName.BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

You can call this procedure and pass the current instance of the Form class as an argument using the following statement:

ChangeFormColor Me
</quote>
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.