Scheduling report printing, any ideas?

[crankyjim]

Hi,

I'm attempting to write an app in VB to automate and schedule the printing of Access reports.

Most is complete except for one thing that's driving me crazy.

If the report is based on a NON-PARAMETER query, everything is fine. As soon as I have a parameter based query, everything comes to a stop waiting for the parameter values to be entered.

My question: Is there a way to search or inspect the underlying query for a report from VB? This way I could ask the user to pre-fill the parameter values and then simply pass them to the query at report runtime.

Thanks for any help.

 

[scriverb]

I have a similar situation. I have solved this problem by providing a parameter table in the application that stores all parameters needed by the reports to be scheduled and run. The parameter table has one record with many fields that can be read at the time of the scheduled run-time for the report and the values stored in Global variables which can then be accessed using a Function call in the queries criteria line.

Example:

Global vDateStart as Date
Function DateStart()
DateStart = vDateStart
End Function

Table: tblDBParameters Field DateStart Date/Time

Run-Time of report:
Dim db as DAO.database
Dim rs as DAO.recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("tblDBParameters", dbOpenDynaset)
rs.movefirst
vDateStart = rs("DateStart&quot
rs.close
db.close
DoCmd.OpenReport "rptYourReportName"

The query for your report should look something like the following:
Select * FROM tblYourTable WHERE TransDate >= DateStart();

This should give you the idea that I am talking about. Just store the necessary parameter values in a one record table and retrieve the parameters and store them in Gloval variables. The Global variable values can be accessed in SQL criteria statements by using a Function call where the Global variable is assiged to the Function Name.

Good luck and I hope this helps you.
Bob Scriver

 

[crankyjim]

Thank you for the suggestion. I'm still wondering though how you determine which query is connected to a report?

I want to be able to give the user a list of reports that can be scheduled and when they pick one, the program should display a list of parameters that the report requires, allowing them to pre-fill the information.

How do you dynamically access the query connected to the report???

If you can answer that, I can sleep once again..

Thanks!


Jim

 

[scriverb]

I would create a form to be used in scheduling the reports. This form would have a combobox with all of the reports listed. It would also have all of the parameter fields in the table I mentioned identified as controls on the form. These controls would be disabled so as not to allow the user to enter any data.

As the User selects a report for Scheduling, the AfterUpdate event procedure of this combobox would enabled the controls that the query for the selected report require user input. The user would then update the data(parameter) fields and thus that particular report is ready to run on the schedule.

Does this make any sense to you? Get back to me if more explanation is necessary. Bob Scriver

 

[crankyjim]

Bob, thanks for the response once again. My difficulty is in finding out which query is used by the selected report.

In this database, the users can create new reports and queries. I need to be able to dynamically determine which query is connected to which report. After I know that, then I can find the parameters, etc... but I just can't see how to find the connection between the report and its underlying query.

I can get a list of report names. I can get a list of querydefs. I just can't figure out how to see which query is connected to which report.

Any assistance is appreciated..

Thanks!


Jim