Maximising report previews

[Peter Hallett]

I have a form running a VBA Do loop that assembles reports. As each report
is created, it is necessary to preview it, to determine whether or not it is
to be printed. This means pausing the Do loop in order to take the necessary
action.

One way of doing this is to open the report in dialogue mode and a second is
to open it from an intermediate form which is itself opened in dialogue mode.
The latter is the more practical because the intermediate form can carry a
pair of command buttons marked ‘Accept’ and ‘Skip’, for example, but both
methods suffer from the same drawback, with different causes.

Report previews are quite small in their default format and therefore often
difficult to read. They generally need to be maximised and, in some cases,
zoomed as well. However, if a report is opened in dialogue mode, its
minimise and maximise buttons are disabled whereas, if it is opened from a
form which was itself opened in dialogue mode then, even though the maximise
and minimise buttons on the report are then enabled, the calling form retains
the focus, which cannot therefore be shifted to the report in order to
operate the buttons! It is not really a Catch 22 situation, but you know
what I mean. Is there a solution that I have failed to spot, or is there
another way of tackling the job?

 

[Marshall Barton]

I have a form running a VBA Do loop that assembles reports. As each report
is created, it is necessary to preview it, to determine whether or not it is
to be printed. This means pausing the Do loop in order to take the necessary
action.

One way of doing this is to open the report in dialogue mode and a second is
to open it from an intermediate form which is itself opened in dialogue mode.
The latter is the more practical because the intermediate form can carry a
pair of command buttons marked ‘Accept’ and ‘Skip’, for example, but both
methods suffer from the same drawback, with different causes.

Report previews are quite small in their default format and therefore often
difficult to read. They generally need to be maximised and, in some cases,
zoomed as well. However, if a report is opened in dialogue mode, its
minimise and maximise buttons are disabled whereas, if it is opened from a
form which was itself opened in dialogue mode then, even though the maximise
and minimise buttons on the report are then enabled, the calling form retains
the focus, which cannot therefore be shifted to the report in order to
operate the buttons! It is not really a Catch 22 situation, but you know
what I mean. Is there a solution that I have failed to spot, or is there
another way of tackling the job?

As I showed in another thread just this week, you can use a
loop to poll the report's existence before proceding in your
main loop. This is not normally a good way to do things,
but when dialog mode ties you in knots, you do what you
gotta do ;-)

Do Until ...
...
stDoc = ...
DoCmd.OpenReport stDoc, ...
Do While CurrentProject.AllReports(stDoc).IsLoaded
DoEvents
Loop
...
Loop

 

[Peter Hallett]

Thanks for the suggestion, Marshal but, unless I am being particularly dim, I
don’t immediately see how it is going to solve the problem. The need is not
to take action if a report exists. It is known to exist. The need is to be
able to halt the code in a way that enables the focus to be shifted to the
report preview in order to use its maximise and zoom buttons so that the
details can be checked to confirm whether it is to be printed or ignored.
(The reports are significant in number, automatically created and then
e-mailed, which makes it all the more important that they can be
individually, and quickly, previewed before dispatch. Sending them off
unseen would be a lot easier but not really appropriate.)

One way of doing the job is to let the invoking Do loop run to completion,
resulting in a pile of previews, stacked on top of one another. Since the
code has not been halted in dialogue mode, the focus can then be transferred
to the pile, allowing the previews to be enlarged, reviewed and closed in
turn. However, this merely shifts the problem further down the line. How
can the associated reports then be individually selected for printing or
deletion? You can’t put command buttons on reports, so you have to use a
form – which puts you back where you started! As I said, not classic Catch
22, but you know what I mean.

I suspect that this one is not soluble in the way I have so far tried.
Unless anyone can suggest an alternative, I think what I may have to do is to
halt the Do loop with an intermediate form, opened in dialogue mode, as at
present, but to include a précis of the current report on the form, along
with the ‘Send’ and ‘Skip’ buttons. (ie use a selection query as the form
source, opening and closing the latter on each loop.) It is not as
convenient as the inbuilt report preview but, as you say, “Ya gotta do what
ya gotta do,†and, in this case, I suspect that Access is not going play
ball. As most of us have discovered, if that is the case, you just have to
find another way.

 

[Stuart McCall]

I have a form running a VBA Do loop that assembles reports. As each report
is created, it is necessary to preview it, to determine whether or not it
is
to be printed. This means pausing the Do loop in order to take the
necessary
action.

One way of doing this is to open the report in dialogue mode and a second
is
to open it from an intermediate form which is itself opened in dialogue
mode.
The latter is the more practical because the intermediate form can carry a
pair of command buttons marked 'Accept' and 'Skip', for example, but both
methods suffer from the same drawback, with different causes.

Report previews are quite small in their default format and therefore
often
difficult to read. They generally need to be maximised and, in some
cases,
zoomed as well. However, if a report is opened in dialogue mode, its
minimise and maximise buttons are disabled whereas, if it is opened from a
form which was itself opened in dialogue mode then, even though the
maximise
and minimise buttons on the report are then enabled, the calling form
retains
the focus, which cannot therefore be shifted to the report in order to
operate the buttons! It is not really a Catch 22 situation, but you know
what I mean. Is there a solution that I have failed to spot, or is there
another way of tackling the job?

Try placing DoCmd.Maximize in the Open event of your report. That way you
don't need access to the Maximize button, and can use the dialog form
method.

 

[Marshall Barton]

You didn't even try my suggestion for the "other way" you
are looking for?

Maybe you are unaware of the DoEvents method and don't
understand its signifiance to your problem. I would think
that you would at least check it out in VBA Help before
dismissing it.

 

[Peter Hallett]

I am sorry if I appeared dismissive. I did not intend to be. I will
certainly give your method a try and report back.
--
Peter Hallett

You didn't even try my suggestion for the "other way" you
are looking for?

Maybe you are unaware of the DoEvents method and don't
understand its signifiance to your problem. I would think
that you would at least check it out in VBA Help before
dismissing it.
--
Marsh
MVP [MS Access]

Thanks for the suggestion, Marshal but, unless I am being particularly dim, I
don’t immediately see how it is going to solve the problem. The need is not
to take action if a report exists. It is known to exist. The need is to be
able to halt the code in a way that enables the focus to be shifted to the
report preview in order to use its maximise and zoom buttons so that the
details can be checked to confirm whether it is to be printed or ignored.
(The reports are significant in number, automatically created and then
e-mailed, which makes it all the more important that they can be
individually, and quickly, previewed before dispatch. Sending them off
unseen would be a lot easier but not really appropriate.)

 

[Peter Hallett]

Well, here, as promised, are the results. You were quite right to upbraid me
for being too hasty. I clearly did not appreciate what I was looking at. I
have never encountered the method before. “Pearls before swine, you might
observe.†Having now had my supper, which consisted of a large piece of
humble pie, I can now report back.

As you predicted, the Do While CurrentProject.AllReports(stDoc).IsLoaded
loop eliminates the need to open the intermediate form in dialogue mode,
hence making the maximise and minimise buttons, on the report preview,
accessible. However, unless I have done something wrong, there are
limitations. Transfer of control to the operating system appears to mean
that it is doing the looping, waiting for the current event to end. This
means that the cursor goes berserk, rapidly alternating between normal and
hour-glass style. That does not make it unusable – the Skip and Send buttons
on the intermediate form are selectable, for example – but it looks decidedly
odd. Another interesting feature is that, although the report preview can be
maximised, it cannot then be zoomed. Nevertheless, being able to enlarge it
from the default size is a big improvement.

You might be interested in my reply to Stuart McCall. In the mean time,
should I have missed some essential point, or appear dismissive of any of
your future responses I am sure you will say so – assuming, of course, that
you are kind enough to respond. If nothing else, this exchange has certainly
expanded my knowledge of VBA. Many thanks.

--
Peter Hallett

You didn't even try my suggestion for the "other way" you
are looking for?

Maybe you are unaware of the DoEvents method and don't
understand its signifiance to your problem. I would think
that you would at least check it out in VBA Help before
dismissing it.
--
Marsh
MVP [MS Access]

Thanks for the suggestion, Marshal but, unless I am being particularly dim, I
don’t immediately see how it is going to solve the problem. The need is not
to take action if a report exists. It is known to exist. The need is to be
able to halt the code in a way that enables the focus to be shifted to the
report preview in order to use its maximise and zoom buttons so that the
details can be checked to confirm whether it is to be printed or ignored.
(The reports are significant in number, automatically created and then
e-mailed, which makes it all the more important that they can be
individually, and quickly, previewed before dispatch. Sending them off
unseen would be a lot easier but not really appropriate.)

 

[Peter Hallett]

--
Peter Hallett

Try placing DoCmd.Maximize in the Open event of your report. That way you
don't need access to the Maximize button, and can use the dialog form
method.


I have now had chance to compare Marshall’s solution with yours. The results are similar. Both methods allow the report previews to be maximised but not then zoomed. Nevertheless, the outcomes represent a significant improvement on the default view. If even greater visibility is eventually needed, I might have to resort to writing a special form to display a précis of the report data but, in the mean time, the increased size of the display may well prove sufficient. It never occurred to me to use the maximise method – but then, that is why I ask questions on this forum. The resulting learning curve is gratifyingly steep, even though, on occasions, I feel that I have missed the obvious.

The advantage of your method, as you are aware, is that the intermediate
form can be opened in dialogue mode, preventing the cursor from behaving as
though it had St. Vitus’s dance. One snag, that was easily resolved, was the
discovery that, if the report preview was maximised on opening, the
switchboard also appeared maximised when control was returned to it after all
the reports had been previewed but that was easily fixed by a DoCmd.Restore
on closing each report. Both DoCmd.Maximise and Restore are rather blunt
instruments but providing that the appropriate object has the focus when they
are run, they seem to be particularly effective.

 

[Stuart McCall]

Ah yes I forgot about the zoom requirement. Well this is possible easily
too. You can follow the Maximize command with RunCommand, using one of the
acCmdZoomxx arguments. Switch to the VBE, open the immediate window and
type:

DoCmd.RunCommand acCmdz

What you see in the intellisense drop-down are the values you can play with
till the report looks right in preview

The advantage of your method, as you are aware, is that the intermediate
form can be opened in dialogue mode, preventing the cursor from behaving
as
though it had St. Vitus’s dance. One snag, that was easily resolved, was
the
discovery that, if the report preview was maximised on opening, the
switchboard also appeared maximised when control was returned to it after
all
the reports had been previewed but that was easily fixed by a
DoCmd.Restore
on closing each report. Both DoCmd.Maximise and Restore are rather blunt
instruments but providing that the appropriate object has the focus when
they
are run, they seem to be particularly effective.

If you are happy using windows api code, I can post a small module which
will enable the report to resize itself to fit the whole client area of
Access. This doesn't involve maximizing, so no restoring is required. Let me
know if you'd like to try that.

 

[Peter Hallett]

Stuart,

Thank you for your kind offer to provide additional assistance. To be
honest, I have never used api code and even though, as an old dog, I am
prepared to learn new tricks, I wonder whether much more effort expended on
this problem can be justified.

I mentioned the zoom feature simply because it was conspicuous by its
absence but one of its problems, as you are well aware, is that it increases
the number of key strokes needed, often significantly. That’s not much of a
drawback when there are only a few reports to preview but when you are
talking of more than 100, things can look rather different. When the user has
gained more confidence, she may well elect for auto-run but, in the early
stages she will want to preview the output, if for no other reason than to
confirm that the expected reports are being sent. In the interim, the simple
expedient of automatically maximising the display will hopefully do the trick
but I suspect that I will eventually have to provide a form-based précis, as
I suggested earlier. The problem with report preview is that, with a lot of
information to display, particularly when a major proportion of it comprises
headers and footers containing material of little relevance to the sender,
the essential text inevitably tends to be rather small when displayed in a
small window. Extracting and displaying it without the ‘padding’ is a good
way of increasing readability. I’ll see how it goes.

In the mean time, I must again express my gratitude for all the people,
including yourself, who are prepared to offer assistance in this forum. Even
though not every suggestion is used, it is a very good way of learning more
about Access – and, at least from my perspective, there certainly seems to be
an awful lot left to learn! In the mean time, if you have anything that you
feel I may find appropriate, or just interesting, do please post it.

 

[Marshall Barton]

As you predicted, the Do While CurrentProject.AllReports(stDoc).IsLoaded
loop eliminates the need to open the intermediate form in dialogue mode,
hence making the maximise and minimise buttons, on the report preview,
accessible. However, unless I have done something wrong, there are
limitations. Transfer of control to the operating system appears to mean
that it is doing the looping, waiting for the current event to end. This
means that the cursor goes berserk, rapidly alternating between normal and
hour-glass style. That does not make it unusable – the Skip and Send buttons
on the intermediate form are selectable, for example – but it looks decidedly
odd. Another interesting feature is that, although the report preview can be
maximised, it cannot then be zoomed. Nevertheless, being able to enlarge it
from the default size is a big improvement.

I have tried to reproduce the issues you described, but I
can't get any of them to happen.

The only thing that even seems remotely related to your
jumping cursor is if one of your open forms is running a
timer event that does something to cause it.

 

[Peter Hallett]

Marsh,

As the result of your latest response, I revisited your method and tried to
reproduce the schizophrenic cursor syndrome I reported earlier. Like you, I
failed. I don’t know what I did last time. Possibly put the intermediate
form inside the CurrentProject loop, although I cannot imagine why I would
have done such a thing. However, having now spent more time and taken more
care I can report virtually 100% success. How glad I am that you stuck with
it, despite my earlier rather cool reception. As I said then, I clearly did
not appreciate what I was looking at.

I have now recast the code so that the innermost loop looks as follows:-

Do
DoCmd.OpenReport stRptName, acViewPreview
DoCmd.OpenForm "frm_Skip_Mail"
Do While CurrentProject.AllReports(stRptName).IsLoaded
DoEvents
Loop
Loop Until …

Frm_Skip_Send is a very small pop-up carrying just two command buttons,
‘Skip’ and ‘Mail’. This form is located in the lower right corner of the
screen, where it does not obscure any of the important part of the display,
including the calling form on which the above code resides, or the report
preview.

Because the CurrentProject loop now effectively pauses code execution,
frm_Skip_Mail does not have to be opened in dialogue mode, and neither, of
course, does the report, meaning that the maximise and minimise buttons on
the latter are fully operational, as is the zoom feature.

Being a pop-up, frm_Skip_Mail allows control to be retained even when the
report preview is maximised, hiding the much larger calling form from which
frm_Skip_Mail is called. That is pretty cool, I have to say.

Not only do I now have a very effective answer to what appeared to be an
intractable problem but I have also learned a great deal, including not to
dismiss suggestions that I do not at first fully understand! Thanks again.

 

[Marshall Barton]

As the result of your latest response, I revisited your method and tried to
reproduce the schizophrenic cursor syndrome I reported earlier. Like you, I
failed. I don’t know what I did last time. Possibly put the intermediate
form inside the CurrentProject loop, although I cannot imagine why I would
have done such a thing. However, having now spent more time and taken more
care I can report virtually 100% success. How glad I am that you stuck with
it, despite my earlier rather cool reception. As I said then, I clearly did
not appreciate what I was looking at.

I have now recast the code so that the innermost loop looks as follows:-

Do
DoCmd.OpenReport stRptName, acViewPreview
DoCmd.OpenForm "frm_Skip_Mail"
Do While CurrentProject.AllReports(stRptName).IsLoaded
DoEvents
Loop
Loop Until …

Frm_Skip_Send is a very small pop-up carrying just two command buttons,
‘Skip’ and ‘Mail’.

Glad to hear that it's all working.

If that popup form stays open all the time and it is modal
then it could be the cause of things jumping around.
AFAICT, there is no reason for it to be modal.

You would only need to open the form inside the loop if both
of its buttons close the form after doing their other tasks.

 

[Peter Hallett]

No, the pop-up isn't modal. If it were, I would not be able to use the
maximise buttons on the report previews. It also closes itself after
carrying out the action specified for each of its two command buttons. I did
not want to finish up opening multiple copies of the intermediate form. This
seems to be OK and I must say that, having got it working so well, I am
loathe to tamper. As I have already discovered to my cost, when it goes
wrong it can do so quite dramatically, so it is probably best to let sleeping
dogs lie and be grateful for such a satisfactory outcome. At one point, I
tried opening the report from within the pop-up - not a good idea as it
transpired.

 

[Marshall Barton]

No, the pop-up isn't modal. If it were, I would not be able to use the
maximise buttons on the report previews. It also closes itself after
carrying out the action specified for each of its two command buttons.

In that case, I believe your final code is the right way to
do it.