Switching report to Print Preview locks up Access!

[Petrol]

I have a report which I open in Report View, but I want the user to be able to switch to Print Preview. So I have a "Preview" command button with a very simple event procedure:

Private Sub cmdPreviewButton_Click()
DoCmd.OpenReport Screen.ActiveReport.Name, acViewPreview
DoEvents
End Sub

This works fine, except that having switched to Print Preview I find the entire Access window is frozen. Nothing I right- or left-click on works - including even the Access window close button. F11 and Ctrl-G still work, but even after using them mouse clicks are inactive.
However, if I take the focus off the Access window and back again - either by clicking outside the window and then inside it again, or by Alt-Tab twice - everything becomes OK again.
It makes no difference whether the report is opened initially through VBA or by clicking in the Navigation pane.
(The DoEvents command was added to the Sub in the hope of solving the problem, but it didn't.)

[PhilOfWalton]

Assuming this cmdPreviewButto n is on a form, as soon as you move the focus to this form, you no longer have an Active report (That must have the focus to be active)

So what you have to do is see which report is Loaded, and prevew that

Private Sub cmdPreviewButton_Click(

    Dim i As Integer
    
    For i = 0 To CurrentProject.AllReports.Count - 1
        If CurrentProject.AllReports(i).IsLoaded Then
            DoCmd.OpenReport CurrentProject.AllReports(i).Name, acViewPreview
            Exit Sub
        End If
    Next i
    
    MsgBox "There are no open reports"
End Sub

Phil

[Petrol]

But no, the cmdPreviewButto n is not on a form, it is on the report which I want to preview and which therefore still has the focus. It is one of three buttons on that report (they only appear in Report mode, or course, not in Print Preview). The others are cmdPrint and cmdClose, which both work fine. (cmdPrint runs an Inputbox to ask how many copies you want, then invokes DoCmd.Printout, and cmdClose obviously just does DoCmd.Close). In fact all three buttons do what they're supposed to, but the Preview one also almost freezes the window. (I say "almost" because the F11 and Ctrl-G keys still work, and the report can still be scrolled with the mouse wheel. But nothing I click on works!

[PhilOfWalton]

Well, I've never put buttons on a report, and my gut feeling is that it's a bad idea. I am somewhat surprised that any of them work. A report is a piece of paper waiting to be printed.

The ribbon covers all that you want (albeit not very neatly) so I suggest you have a look at a customised ribbon

Phil

[Petrol]

Yes, I may have to do that. But I have buttons on most of my many forms allowing the user to do things, and from the user's point of view a report in report view and a form look much the same on screen, so it's a logical thing to want to do. And what could be more obvious and intuitive when looking at a report on screen than to have buttons to print it or to close it? I don't like the user to have to right-click on the tab, or go anywhere outside the window that they are looking at.

Ah well, we have to live within the constraints of the system, I guess :(.

[Petrol]

But actually, I'd still like to try to find a way of doing it if I can. There's nothing I can see in any documentation that says you shouldn't put buttons on Reports, and Access cleverly makes them visible in Report view but hides them in Print Preview, so ....
I wonder of anyone else has had any experience with this?

[Petrol]

Also I've seen references in other websites to people putting buttons on reports successfully.

So eventually I put an unwanted MsgBox (vbOKonly) in an event on the switch to Preview. This forces the user to click in the MsgBox window and when that closes and he comes back to the main window all is well.
But it's a silly thing to have to do ...

[PhilOfWalton]

I think, Peter, that if you have a report, it is probably with the intention of printing it which will probably done from Print Preview.

A command button will show up and work on a report in Report View, but not Print Preview.

I think I must ask why initially you want to open the report in Report View in the first place?

Phil

[PhilOfWalton]

I think, Peter, that if you have a report, it is probably with the intention of printing it which will probably done from Print Preview.

A command button will show up and work on a report in Report View, but not Print Preview.

I think I must ask why initially you want to open the report in Report View in the first place?

Phil

[Petrol]

Good question. The answer is that I have lots of reports but many of them are not normally going to be printed. They are just to show the relevant formatted data on screen, and it's easier to scroll through if it's in report view than if it's in a lot of page images. But some will be printed, and for consistency of user view and flexibility I have a consistent set of buttons (Preview, Print, Close) on all of them and leave it to the user to decide if he wants to print them.

And yes, you're right, as I said in #3 and #6 the buttons only show up in report view. That's one of the things that makes me believe they're supposed to work!

But getting back to the original question, have you any idea why the window freezes?

[PhilOfWalton]

Yes, Peter, I get exactly the same as you even when I change the code to

    DoCmd.OpenReport Me.Name, acViewPreview

Me.Name is more rubust then Screen.ActiveRe port.
I haven't the foggiest why there is a problem, perhaps the real experts would like to comment.

I know this is a cop out but it works

Private Sub CmdPreview_Click()

    DoCmd.Close acReport, Me.Name

    DoCmd.OpenReport "MyReport", acViewPreview

End Sub

"MyReport is the Report' name, you can't use Me.Name as the report is closed so "no longer exits"

Phil

[NeoPa]

Not very expert in this area Phil, but your code might be amended to the following to avoid the need to include the name as a literal :

Private Sub CmdPreview_Click()
    Dim strName As String

    strName = Me.Name
    Call DoCmd.Close(ObjectType:=acReport, ObjectName:=strName, Save:=acSaveNo)
    Call DoCmd.OpenReport(ReportName:=strName, View:=acViewPreview)
End Sub

[PhilOfWalton]

Agreed, that is a much more universal way of handling all his reports

Phil

[Petrol]

Excellent, Gentlemen. Thank you both.I think I'll have to mark Phil's idea of closing and reopening as the best answer, but I'll use a modified version NeoPa's generalised version.

Why modified? Because the Preview Sub is actually a public sub in a separate code module so it can be called from lots of reports. Each one has a cmdPreview_Clic k routine that simply calls the common sub. And it turns out that you can't use Me in the common sub - it gets "Invalid use of Me keyword". So I have to pass the report name as a parameter, which means changing all the calls. But it works! So thanks again.

Just one question, NeoPa: Is it better practice to use the form

Call DoCmd.Close(ObjectType:=acReport, ObjectName:=strName, Save:=acSaveNo)

, and if so, why? I've always just used

DoCmd.Close acReport, strReportName, acSaveNo

.

[NeoPa]

Marking Phil's answer as best seems eminently sensible.

There are two main differences between the two versions.

1. The use of Call and parentheses.
2. The use of named parameters.

I prefer to use #1 because it makes it clearer to a reader of the code that a procedure of some kind is being called. Most people don't even know that Date() and CurrentDb() are even Function Procedures and simply assume them to be Variables.

I prefer to use #2 because a reader can tell pretty darn quickly what's going on. How many times have we seen references with a list of parameters where some are missing, represented simply by commas (,)? Much easier to follow when Named Parameters are used. Not necessarily for every single procedure, but any where the parameters aren't immediately known or where there are more than a couple.