code fires before report fully painted-- how to prevent?

[Guest]

I have a form that give the user the opportunity to view a report as the
usual Access report or as a Powerpoint presentation. The code itself works
as expected; however, I have a timing problem.

I need the report opened in order to update certain aspects and then grab a
chart from it. Once the report is opened my code copies the chart from the
Access report and pastes it into a Powerpoint slide. Unfortunately, even
though I have the code sequenced properly, the graph is grabbed before the
report is opened and updated. Thus a generic version of the graph that
doesn't represent the user's intent is wrongly used.

If I manually open the report first and then fire off the Powerpoint code,
everything works fine. So apparently I need to somehow implement code in my
form that tests for the report to be completely opened before kicking off the
Powerpoint code. Maybe a While/Wend would work, but what condition am I
looking for?

Thanks all,

Randall Arnold

 

[Dirk Goldgar]

I have a form that give the user the opportunity to view a report as
the usual Access report or as a Powerpoint presentation. The code
itself works as expected; however, I have a timing problem.

I need the report opened in order to update certain aspects and then
grab a chart from it. Once the report is opened my code copies the
chart from the Access report and pastes it into a Powerpoint slide.
Unfortunately, even though I have the code sequenced properly, the
graph is grabbed before the report is opened and updated. Thus a
generic version of the graph that doesn't represent the user's intent
is wrongly used.

If I manually open the report first and then fire off the Powerpoint
code, everything works fine. So apparently I need to somehow
implement code in my form that tests for the report to be completely
opened before kicking off the Powerpoint code. Maybe a While/Wend
would work, but what condition am I looking for?

Thanks all,

Randall Arnold

I don't know exactly how you're doing this, or whether that's the best
way to get your chart, but as far as waiting until the report is ready
is concerned, is there an event in the report that will not fire until
the chart is ready for grabbing? If so, you could use code in that
event either to call "Powerpoint code" directly, or else to set a global
flag variable. The former approach would probably be best, because if
you take the latter approach you still have to have a loop (with
DoEvents) in your form code to wait until the flag variable is set.

 

[Guest]

I'm actually now trying to use the approach you just mentioned. I added a
boolean property to my form called "PowerPoint"; it's set to True when the
user clicks the proper button and the report is opened. The event to use for
the copy/paste code would be the ReportFooter.Print event I think (the graph
is in that section); however, I'm experiencing a problem there, too. That
event never fires until the user clicks to the last page of the report (makes
sense). In my case, the report is opened in a hidden state, and I don't see
how to programmatically force the report to the last page once it's fully
opened. Ironically, this is a catch-22 similar to my existing problem.

As for my code, it's very simple (here's the critical stuff):

Set objApp = CreateObject("Powerpoint.Application")
Set objDoc = objApp.Presentations.Add(msoTrue)
strReport = "Order Setup Defects Report"
DoCmd.OpenReport strReport, acViewPreview, , strFilter, acHidden
'
' various changes made to report graph here based on user selections
'
Set objSlide = objDoc.Slides.Add(1, ppLayoutTitleOnly)
Set objGraph = Reports(strReport)!Summary_chartobj.Object
objGraph.ChartArea.Copy
objDoc.Slides(1).Shapes.Paste

The copy/paste code executes immediately after the OpenReport action, well
before the report is actually fully loaded. Setting the report to modal
doesn't help, unfortunately. IMO this is a flaw in Access; I believe
developers should have the ability to halt further execution of code pending
a report opening.

I'll keep trying though...

 

[Guest]

In case anyone else ever needs this, I found the (partial) solution on a few
websites.

The trick is to throw code execution into a loop while the report is open.
The following code does this:

DoCmd.OpenReport "MyReport, A_PREVIEW"
'Me.Visible = False
DoEvents
While SysCmd(SYSCMD_GETOBJECTSTATE, A_REPORT, "MyReport") = OBJSTATE_OPEN
DoEvents
Wend
'Me.Visible = True
DoEvents

I place my Powerpoint slide creation code within the while/wend loop. My
last statement closes the report, creating a false condition for
OBJSTATE_OPEN and exiting the while/wend.

One problem remains in that I need to force the report to the last page, but
this gives me hope that I can figure that one out!

Helpful websites:

http://www.accessdatabasetips.com/report-preview-1.html
http://microsoftaccessconsultant.com/reports/ReportOpenInFrontOfDialog.htm

Randall Arnold

 

[Dirk Goldgar]

I'm actually now trying to use the approach you just mentioned. I
added a boolean property to my form called "PowerPoint"; it's set to
True when the user clicks the proper button and the report is opened.
The event to use for the copy/paste code would be the
ReportFooter.Print event I think (the graph is in that section);
however, I'm experiencing a problem there, too. That event never
fires until the user clicks to the last page of the report (makes
sense). In my case, the report is opened in a hidden state, and I
don't see how to programmatically force the report to the last page
once it's fully opened. Ironically, this is a catch-22 similar to my
existing problem.

I think you should use the section's Format event, not the Print event.
As for forcing the event to fire without user intervention, you can make
that happen by including a text box on your report -- anywhere, and it
need not be visible -- with a controlsource that refers to the report's
Pages property. This is most commonly seen in a page-footer text box
with the controlsource:

="Page " & [Page] & " of " & [Pages]

but all you really need is

=[Pages]

In order to know how many pages the report has, Access has to format all
the pages, including the Report Footer. Note that pages and sections
may be formatted more than once, so you'll want to use a flag to make
sure your PowerPoint code isn't executed more than once.

So you might have code like this for the report's module:

'---- start of suggested report module code -----
Dim mblnPowerPoint As Boolean ' true if Powerpoint is wanted

Private Sub Report_Open(Cancel As Integer)

' See if PowerPoint flag should be set
If CurrentProject.AllForms("YourFormName").IsLoaded Then
mblnPowerPoint = Forms("YourFormName")!PowerPoint
End If

End Sub

Private Sub ReportFooter_Format( _
Cancel As Integer, FormatCount As Integer)

Dim objApp As Object
Dim objDoc As Object
Dim objSlide As Object
Dim objGraph As Object

If mblnPowerPoint Then
mblnPowerPoint = False ' Switch off flag

Set objApp = CreateObject("Powerpoint.Application")
Set objDoc = objApp.Presentations.Add(msoTrue)
Set objSlide = objDoc.Slides.Add(1, ppLayoutTitleOnly)
Set objGraph = Me!Summary_chartobj.Object
objGraph.ChartArea.Copy
objDoc.Slides(1).Shapes.Paste

' ... other code related to these objects ...

Set objGraph = Nothing
Set objSlide = Nothing
Set objDoc = Nothing
Set objApp = Nothing

End If

End Sub
'---- end of suggested report module code -----

That's just air code, but I think something along those lines ought to
work.

 

[Dirk Goldgar]

In case anyone else ever needs this, I found the (partial) solution
on a few websites.

The trick is to throw code execution into a loop while the report is
open. The following code does this:

DoCmd.OpenReport "MyReport, A_PREVIEW"
'Me.Visible = False
DoEvents
While SysCmd(SYSCMD_GETOBJECTSTATE, A_REPORT, "MyReport") =
OBJSTATE_OPEN DoEvents
Wend
'Me.Visible = True
DoEvents

I place my Powerpoint slide creation code within the while/wend loop.
My last statement closes the report, creating a false condition for
OBJSTATE_OPEN and exiting the while/wend.

One problem remains in that I need to force the report to the last
page, but this gives me hope that I can figure that one out!

I think you can do better than this solution. See my reply earlier in
this thread.

 

[Guest]

Actually I did do better-- all it took was a simple DoEvents after opening
the report from my form to momentarily halt further code execution. I don't
need the syscmd, and no additional code or code changes were required in the
report events. Everything works perfectly now! I'm surprised this didn't
occur to me from the start.... sigh.

Thanks, Dirk.

Randall

 

[Guest]

Thanks again Dirk for your suggestions.

I continued to encounter problems with getting the graph in the proper state
with code in the report itself (using code very similar to yours before you
posted it). It appears I really do need to keep the Powerpoint code
external, ie, running from the user form. Turns out a simple DoEvents after
DoCmd.OpenReport was all I needed to make sure the report was fully open
before proceeding, and then using Sendkeys to simulate pressing the End key
(followed by another necessary DoEvents) to force the report to the last
page. From there my Powerpoint code executes flawlessly, to wit:

Private Sub Powerpoint_button_Click()
Dim objDoc As PowerPoint.Presentation
Dim objSlide As PowerPoint.Slide
Set objApp = CreateObject("Powerpoint.Application")
Set objDoc = objApp.Presentations.Add(msoTrue)
DoCmd.OpenReport strReport, acViewPreview, , strFilter
DoEvents
SendKeys "{End}"
DoEvents
Reports(strReport).Visible = False
Set objSlide = objDoc.Slides.Add(1, ppLayoutTitleOnly)
Set objGraph = Reports(strReport)!Summary_chartobj.Object
objGraph.ChartArea.Copy
objDoc.Slides(1).Shapes.Paste
'
' etc etc etc
'
Set objSlide = Nothing
Set objDoc = Nothing
objApp.Visible = True
DoCmd.Close acReport, strReport
Set objDoc = Nothing
Set objSlide = Nothing
End Sub

There's additional filtering and formatting code of course, but that
provides the gist of what I'm doing. I'm just glad it's working! Now I need
to create similar routines for Word, Publisher, etc. This framework should
transfer to any office app, with the appropriate modifications of course.

Randall

I'm actually now trying to use the approach you just mentioned. I
added a boolean property to my form called "PowerPoint"; it's set to
True when the user clicks the proper button and the report is opened.
The event to use for the copy/paste code would be the
ReportFooter.Print event I think (the graph is in that section);
however, I'm experiencing a problem there, too. That event never
fires until the user clicks to the last page of the report (makes
sense). In my case, the report is opened in a hidden state, and I
don't see how to programmatically force the report to the last page
once it's fully opened. Ironically, this is a catch-22 similar to my
existing problem.

I think you should use the section's Format event, not the Print event.
As for forcing the event to fire without user intervention, you can make
that happen by including a text box on your report -- anywhere, and it
need not be visible -- with a controlsource that refers to the report's
Pages property. This is most commonly seen in a page-footer text box
with the controlsource:

="Page " & [Page] & " of " & [Pages]

but all you really need is

=[Pages]

In order to know how many pages the report has, Access has to format all
the pages, including the Report Footer. Note that pages and sections
may be formatted more than once, so you'll want to use a flag to make
sure your PowerPoint code isn't executed more than once.

So you might have code like this for the report's module:

'---- start of suggested report module code -----
Dim mblnPowerPoint As Boolean ' true if Powerpoint is wanted

Private Sub Report_Open(Cancel As Integer)

' See if PowerPoint flag should be set
If CurrentProject.AllForms("YourFormName").IsLoaded Then
mblnPowerPoint = Forms("YourFormName")!PowerPoint
End If

End Sub

Private Sub ReportFooter_Format( _
Cancel As Integer, FormatCount As Integer)

Dim objApp As Object
Dim objDoc As Object
Dim objSlide As Object
Dim objGraph As Object

If mblnPowerPoint Then
mblnPowerPoint = False ' Switch off flag

Set objApp = CreateObject("Powerpoint.Application")
Set objDoc = objApp.Presentations.Add(msoTrue)
Set objSlide = objDoc.Slides.Add(1, ppLayoutTitleOnly)
Set objGraph = Me!Summary_chartobj.Object
objGraph.ChartArea.Copy
objDoc.Slides(1).Shapes.Paste

' ... other code related to these objects ...

Set objGraph = Nothing
Set objSlide = Nothing
Set objDoc = Nothing
Set objApp = Nothing

End If

End Sub
'---- end of suggested report module code -----

That's just air code, but I think something along those lines ought to
work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks again Dirk for your suggestions.

I continued to encounter problems with getting the graph in the
proper state with code in the report itself (using code very similar
to yours before you posted it). It appears I really do need to keep
the Powerpoint code external, ie, running from the user form. Turns
out a simple DoEvents after DoCmd.OpenReport was all I needed to make
sure the report was fully open before proceeding, and then using
Sendkeys to simulate pressing the End key (followed by another
necessary DoEvents) to force the report to the last page. From there
my Powerpoint code executes flawlessly, to wit:

Private Sub Powerpoint_button_Click()
Dim objDoc As PowerPoint.Presentation
Dim objSlide As PowerPoint.Slide
Set objApp = CreateObject("Powerpoint.Application")
Set objDoc = objApp.Presentations.Add(msoTrue)
DoCmd.OpenReport strReport, acViewPreview, , strFilter
DoEvents
SendKeys "{End}"
DoEvents
Reports(strReport).Visible = False
Set objSlide = objDoc.Slides.Add(1, ppLayoutTitleOnly)
Set objGraph = Reports(strReport)!Summary_chartobj.Object
objGraph.ChartArea.Copy
objDoc.Slides(1).Shapes.Paste
'
' etc etc etc
'
Set objSlide = Nothing
Set objDoc = Nothing
objApp.Visible = True
DoCmd.Close acReport, strReport
Set objDoc = Nothing
Set objSlide = Nothing
End Sub

There's additional filtering and formatting code of course, but that
provides the gist of what I'm doing. I'm just glad it's working!
Now I need to create similar routines for Word, Publisher, etc. This
framework should transfer to any office app, with the appropriate
modifications of course.

SendKeys is buggy and error-prone, and I would go to considerable
lengths to avoid using it. But if you're happy, I'm happy.

 

[Guest]

Yeah, I've had numerous problems with Sendkeys in screen-scraper apps I've
written in the past (out of desperation...lol). I'm pretty sure there's a
more robust way to force the report to the last page, but I just haven't
found it. I think the trick would be to gain direct access to the page
control at the bottom left of the report window but so far searches on that
topic have turned up nothing but a promising link to an article I can't
access (sigh). But now that I at least have a workable solution I can spend
time later on code improvements.

Randall

 

[Dirk Goldgar]

Yeah, I've had numerous problems with Sendkeys in screen-scraper apps
I've written in the past (out of desperation...lol). I'm pretty sure
there's a more robust way to force the report to the last page, but I
just haven't found it. I think the trick would be to gain direct
access to the page control at the bottom left of the report window
but so far searches on that topic have turned up nothing but a
promising link to an article I can't access (sigh). But now that I
at least have a workable solution I can spend time later on code
improvements.

Did you actually try what I suggested, including placing a control with
a reference to the [Pages] property on the report? I believe that
shouild work.

 

[Guest]

I already had that control in the reportfooter from the beginning. I think I
posted in another reply that setting the page property to the last page has
had no effect, regardless of where I implemented it. So I gave up on that
approach.

Randall

Yeah, I've had numerous problems with Sendkeys in screen-scraper apps
I've written in the past (out of desperation...lol). I'm pretty sure
there's a more robust way to force the report to the last page, but I
just haven't found it. I think the trick would be to gain direct
access to the page control at the bottom left of the report window
but so far searches on that topic have turned up nothing but a
promising link to an article I can't access (sigh). But now that I
at least have a workable solution I can spend time later on code
improvements.

Did you actually try what I suggested, including placing a control with
a reference to the [Pages] property on the report? I believe that
shouild work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I already had that control in the reportfooter from the beginning. I
think I posted in another reply that setting the page property to the
last page has had no effect, regardless of where I implemented it.
So I gave up on that approach.

But explicitly setting the Page property has nothing to do with it.
Having a control that refers to the [Pages] -- not [Page] -- property
forces the report to format the last page of the report before
displaying anything, thus firing the Format event -- not the Print
event -- of the Report Footer section without any user intervention.
This definitely works. That's why I think you ought to be able to use
the Format event of that report section to either execute your code
directly, or set a flag that your code could test (the code would have
to be looping with DoEvents while waiting).

Maybe there's some reason this approach wouldn't work. I can certainly
imagine that there could be something about the chart object that would
keep the PowerPoint copy/paste code from working in the Format event
itself, but if it did work it would be a clean, event-driven solution to
the problem. However, it hasn't been clear to me from what you've
posted so far that you actually tried it. That's the only reason I'm
still going on about it.

 

[Guest]

Maybe I'm just not communicating effectively. I *did* try the approach you
recommended (see previous reply), before you posted the code. The chart
(graph) object was still not updating, even though the format (and print)
events were firing for the reportfooter. I am not sure why this is. Bottom
line, I *only* get a completely updated chart once the report has fully
rendered AND the last page is exposed. I strongly suspect this may have to
do with the query embedded in the chart object, which has 4 parameters that
must be satisfied to properly determine chart appearance (these are grabbed
automatically from the user form). If the form is not loaded, I have code
that sets defaults but I wasn't concerned about that when I originally
posted; I needed to get the part working that was dependent on the parameters.

I apologize if I inadvertantly omitted information that would aid your
understanding of this issue, especially with regards to why code in the
report wasn't doing the trick. Although I have many years experience with
this stuff I'm in some new territory here and wasn't sure what was preventing
me from attaining the results I desired and thus what I needed to cover in my
posts. However, all works well now!

Randall

I already had that control in the reportfooter from the beginning. I
think I posted in another reply that setting the page property to the
last page has had no effect, regardless of where I implemented it.
So I gave up on that approach.

But explicitly setting the Page property has nothing to do with it.
Having a control that refers to the [Pages] -- not [Page] -- property
forces the report to format the last page of the report before
displaying anything, thus firing the Format event -- not the Print
event -- of the Report Footer section without any user intervention.
This definitely works. That's why I think you ought to be able to use
the Format event of that report section to either execute your code
directly, or set a flag that your code could test (the code would have
to be looping with DoEvents while waiting).

Maybe there's some reason this approach wouldn't work. I can certainly
imagine that there could be something about the chart object that would
keep the PowerPoint copy/paste code from working in the Format event
itself, but if it did work it would be a clean, event-driven solution to
the problem. However, it hasn't been clear to me from what you've
posted so far that you actually tried it. That's the only reason I'm
still going on about it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Oops, I goofed!

All I had to do was add a Refresh method to the Graph object after I updated
its properties in the ReportFooter.Format event, and I no longer need the
Sendkeys statement. I also neglected to tell you I'd moved the code to the
Formet event.

Sorry for the confusion. This has finally been reduced to a simple, elegant
solution and I appreciate your suggestions and patience.

Randall

I already had that control in the reportfooter from the beginning. I
think I posted in another reply that setting the page property to the
last page has had no effect, regardless of where I implemented it.
So I gave up on that approach.

But explicitly setting the Page property has nothing to do with it.
Having a control that refers to the [Pages] -- not [Page] -- property
forces the report to format the last page of the report before
displaying anything, thus firing the Format event -- not the Print
event -- of the Report Footer section without any user intervention.
This definitely works. That's why I think you ought to be able to use
the Format event of that report section to either execute your code
directly, or set a flag that your code could test (the code would have
to be looping with DoEvents while waiting).

Maybe there's some reason this approach wouldn't work. I can certainly
imagine that there could be something about the chart object that would
keep the PowerPoint copy/paste code from working in the Format event
itself, but if it did work it would be a clean, event-driven solution to
the problem. However, it hasn't been clear to me from what you've
posted so far that you actually tried it. That's the only reason I'm
still going on about it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)