"Object Invalid or no longer set" on Cmd.Close

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FlBrent - 08 Apr 2008 21:38 GMT

Hi,
I've written a report scheduling tool based on a Form Timer_Event. I
have built a 'report schedule' table which contains many fields I require to
run the reports based on the timer. Anyway, I create a recordset, based on
the reports in the schedule table, and run the reports when it's time for
them to run. I can run the report just fine, based on the report name I pull
from the report schedule table. However, the report runs in a preview window
and I want to close the window after I print it or send it to a file server.
But.. when I attempt to close the report (in the preview window), I get an
"Object Invalid and no longer set" error message. I don't know if it's got
something to do with me looping through a recordset or not, but it doesn't
work for me. Case "03" causes the issue. Please help if you can. Thanks.

Here's a snippet of code:

Select Case ![SchOutput_Type]
Case Is = "00"
MsgBox "Output to Local Printer"
DoCmd.OpenReport ![ReportName], acViewNormal ' to print the report to the
local printer
'DoCmd.OpenReport ![ReportName], acViewPreview, "", "", acNormal ' to view
the report (for testing)
Case Is = "01"
MsgBox "Output to Other Printer"
Case Is = "02"
MsgBox "Output to Email"
Case Is = "03"
MsgBox "Output to File Directory"
DoCmd.OpenReport ![ReportName], acViewPreview, "", "", acNormal
DoCmd.OutputTo acReport, "Del_Notices", "MicrosoftExcelBiff8(*.xls)", !
[OutputPath], False, "", 0
MsgBox "File Saved Successfully"
DoCmd.Close acReport, ![ReportName] ' This line causes the issue
End Select

Reply to this Message

George Nicholson - 09 Apr 2008 19:57 GMT

Any chance you are trying to Close the report before its actually been
opened?

You might want to insert a DoEvents (or some variation of a DoUntil
IsLoaded(![ReportName]) loop) after your OpenReport command to make sure the
report is open before you attempt to Close it.

That said, not sure what purpose is served by opening it and then closing
it. Unless the OutputTo takes a while the user will never see the report and
issuing just the OutputTo command would acheive the same thing.

Signature

HTH,
George

> Hi,
> I've written a report scheduling tool based on a Form Timer_Event. I
[quoted text clipped - 37 lines]
> DoCmd.Close acReport, ![ReportName] ' This line causes the issue
> End Select

Reply to this Message

FlBrent - 09 Apr 2008 20:17 GMT

George,
Thanks for the reply. The reason I'm wanting to close it is because the
report will be run during the night and sent to a file server, so I don't
want the report windows to still be open in the morning, as that would stack
up all the scheduled jobs that ran overnight. Are you saying that the report
window would go away by itself if I just used the OutputTo command? If so, I
just tested it and the preview window still appears, even after the file has
been written, which I do not want.

>Any chance you are trying to Close the report before its actually been
>opened?
[quoted text clipped - 12 lines]
>> DoCmd.Close acReport, ![ReportName] ' This line causes the issue
>> End Select

Reply to this Message

George Nicholson - 09 Apr 2008 22:17 GMT

I'm saying that OutputTo is generating a different report than the report
specified in OpenReport, or at best a 2nd instance of the same report.

If all you want to do is Output it, then just Output it. No need to Open it
first. (The only exception that occurs to me is if you are Outputting
ReportB and ReportB needs a value from ReportA. That is the only thing that
would require ReportA to be open while Outputting.)

VB will not pause between OpenReport, OutputTo and Close unless you tell it
to (which you haven't). VBA will try to execute the next line of code as
soon as it can. When opening reports, form or queries, that can sometimes
mean before processing has finished (VBA has passed the instruction along to
Access but doesn't really know if its been fully completed, just that its a
legal instruction). As written, if your report opens a little slowly, there
is a chance VBA may try to close it before it has finished opening (hence
the error message) and if you want to close it that quickly, i simply
wonder: why bother opening it at all?

On the other hand:

>DoCmd.OpenReport ![ReportName], acViewPreview, "", "", acNormal
> DoCmd.OutputTo acReport, "Del_Notices", "MicrosoftExcelBiff8(*.xls)", !
> [OutputPath], False, "", 0
> MsgBox "File Saved Successfully"
> DoCmd.Close acReport, ![ReportName] ' This line causes the issue

Is there any chance that ![ReportName] is getting changed or becomes invalid
while the report is being Opened or Output? (i.e., Is the form being changed
or closed?)
You might try assigning the value to a variable so you don't need to worry
about it:
strReportName = ![ReportName]
DoCmd.OpenReport strReportName, acViewPreview, "", "", acNormal
'...Output different report or a 2nd instance of strReportName here..
DoCmd.Close acReport, strReportName

For the record, the proper constant for the WindowMode argument of
OpenReport is acWindowNormal. No harm done since it has the same value as
acNormal: zero.

Signature

HTH,
George

> George,
> Thanks for the reply. The reason I'm wanting to close it is because the
[quoted text clipped - 27 lines]
>>> DoCmd.Close acReport, ![ReportName] ' This line causes the issue
>>> End Select

Reply to this Message

FlBrent - 09 Apr 2008 22:32 GMT

George,
I'll have to offer up a big Homer Simpson 'Doh!!!!'. Thanks man. It's
amazing how many times you look at something right in front of you face and
don't see it. That explains everything. I left the OpenReport code in there
(from a previous task). I removed it and it's all good. Thank you so much
for your help!!!

Brent

>I'm saying that OutputTo is generating a different report than the report
>specified in OpenReport, or at best a 2nd instance of the same report.
[quoted text clipped - 40 lines]
>>>> DoCmd.Close acReport, ![ReportName] ' This line causes the issue
>>>> End Select

Reply to this Message