Setting Record Source for a report

[Guest]

HI

I need to set the record source in a report. It appears that this can only be done in the OnOpen event. I need to use the report with several different queries. I can set the record source in the OnOpen event but it seems to only be called once for a given report per session in Access. So doing this with DoCmd.OpenReport does not work as the OnOpen event is not called on the 2nd to nth call, only the first. The idea of opening the form in acViewDesign mode setting the record source then opening in acViewNormal works sometimes but causes Access to crash on others. This is not very satisfactory as it will not work anyway in a .mde. The best I have so far is as follows

Dim Rpt As Repor

gReportDataSource = "qry_NonSPS
gReportFilter = "[ShipDate]>#01/1/2004# AND [ShipDate]<#1/1/2005#
gReportTitle = "Independent Customer Sales 2004 year to date

Set Rpt = New Report_rpt_MyRepor
Rpt.Visible = Tru

The OnOpen event for the new instance of the report is fired and can set the appropriate data from the global variables (hack hack) but it does the job. The problem at this point is that the report displays in a window and I need it to print. DoCmd.Print does not work because the window does not have the focus. DoCmd.SelectObject does not work because the new instance is not in the Forms collection. (Who designed this mess anyway?) I should be able to create a new instance of a class (report) manupulate its properties then run it. Instead it runs in the Set Rpt= statement and after that its too late

Charlie

The handling of reports is strange. The OnOpen Event does not fire each time DoCmd.OpenForm is called even with DoCmd.Close is called for the form before the OpenForm Call.

 

[SA]

Charlie:

When using Select object, specify that you are addressing a report and not
(by default) a form as in:

Docmd.SelectObject acReport, "MyReport"

From there, you can either call

Docmd.RunCommand acCmdPrint 'to bring up the print dialog or
Docmd.PrintOut 'which will send the report to the current default printer
or specific printer the report is set to use.

The only issue with Docmd.PrintOut is that if you are using Access 2000, be
aware that there is a bug with the PrintOut method in mde dbs, where calling
that fails with an error that that function is not currently available.
This was fix in Access 2002 and above.

 

[Marshall Barton]

I need to set the record source in a report. It appears that this can only be done in the OnOpen event. I need to use the report with several different queries. I can set the record source in the OnOpen event but it seems to only be called once for a given report per session in Access. So doing this with DoCmd.OpenReport does not work as the OnOpen event is not called on the 2nd to nth call, only the first. The idea of opening the form in acViewDesign mode setting the record source then opening in acViewNormal works sometimes but causes Access to crash on others. This is not very satisfactory as it will not work anyway in a .mde. The best I have so far is as follows:

Dim Rpt As Report

gReportDataSource = "qry_NonSPS"
gReportFilter = "[ShipDate]>#01/1/2004# AND [ShipDate]<#1/1/2005#"
gReportTitle = "Independent Customer Sales 2004 year to date"

Set Rpt = New Report_rpt_MyReport
Rpt.Visible = True

The OnOpen event for the new instance of the report is fired and can set the appropriate data from the global variables (hack hack) but it does the job. The problem at this point is that the report displays in a window and I need it to print. DoCmd.Print does not work because the window does not have the focus. DoCmd.SelectObject does not work because the new instance is not in the Forms collection. (Who designed this mess anyway?) I should be able to create a new instance of a class (report) manupulate its properties then run it. Instead it runs in the Set Rpt= statement and after that its too late.

CharlieB

The handling of reports is strange. The OnOpen Event does not fire each time DoCmd.OpenForm is called even with DoCmd.Close is called for the form before the OpenForm Call.

This is an area of Access where the implementation doesn't
come up to the design specs, probably because the testing
folks are either unaware of the capability or they just
don't understand it. Whatever the reason, this capability
is so rarely used that its problems just never get a chance
to rise above the noise of other higher profile issues.

The Open event is fired for each instance, it's just that
there's a race condition about which instance is doing it.
A few folks have tried to work out a way to get around the
timing issues of which instance is executing with which set
of properties.

One key to the timing issues is to use one or more(?)
DoEvents just before the Set New statement. This seems(?)
to give the previous instance some time to get itself
organized before another instance bumps into it.

Another important point we've come up with is to stay away
from the Filter property and construct the record source SQL
statement instead.

I have no ideas about how to use OutputTo or PrintOut with
multiple instances.

In some situations, all this might be avoided by using a
single report that groups the data into whatever you're
using to distinguish the separate reports.

 

[Guest]

Marshall

Thanks for your time. You are right in that the events occur strangely but I am still at a loss. I agree this seems to be a bug but I find it hard to believe that the issue was unknown. I don't have the number now but there is an MSDN article that is very close to this issue. It says it effects only Access 97 and is fixed in subsequent versions. If this was too low a priority to fix, I guess the folks as Microsoft did not intend Access for any serious development but rather just for play toys. This is too bad for me

I have a database structure that is not myown. I have what I consider complex queries (selects in selects in selects) to pull the data. They all work. I use aliases within the queries to provide a uniform interface to the report writer. After all the work to design this the report writer does not work because the record source must be set essentially at design time (it can be set at run time but only once which to me is virtually the same

Still I am almost there. Using the code I provided I can DISPLAY what I want multiple different times but I cannot get it to PRINT. Docmd select object followed by DoCmd print does not work. I get errors the SECOND time (after a change of record source

Anyway
Thanks again for you time spent answering

Charlie

Thanks again for your tim
Charlie B

 

[Marshall Barton]

Marshall,

Thanks for your time. You are right in that the events occur strangely but I am still at a loss. I agree this seems to be a bug but I find it hard to believe that the issue was unknown. I don't have the number now but there is an MSDN article that is very close to this issue. It says it effects only Access 97 and is fixed in subsequent versions. If this was too low a priority to fix, I guess the folks as Microsoft did not intend Access for any serious development but rather just for play toys. This is too bad for me.

I have a database structure that is not myown. I have what I consider complex queries (selects in selects in selects) to pull the data. They all work. I use aliases within the queries to provide a uniform interface to the report writer. After all the work to design this the report writer does not work because the record source must be set essentially at design time (it can be set at run time but only once which to me is virtually the same)

Still I am almost there. Using the code I provided I can DISPLAY what I want multiple different times but I cannot get it to PRINT. Docmd select object followed by DoCmd print does not work. I get errors the SECOND time (after a change of record source)

You sure have zeroed in on a couple of sore points. The
problem is that DoCmd has no way to specify the specific
instance of the object you want to operate on. It would be
more appropriate for the report instance to have Open,
Close, Printout, SendTo, etc. methods, but that's not the
way the current Object Model is set up.

Like I said before, I've only ever had to preview the report
instances and let the user use the menu/tool bar to close or
print the report, so I can't provide any help with this
aspect of your question.