Passing parameters to a report programmatically

  • Thread starter Thread starter Geraldine Hobley
  • Start date Start date
G

Geraldine Hobley

Hello,
I have a report called MyLetter, it is bound to a
stored procedure called MyStoredProcedure with parameters
@Order_id and @job_id

Currently the recordsource - MyStoredProcedure
and input Parameters - @order_id AND @Job_id are set in
the properties of the report, and the report is run as
follows

Dim Report_Name As String

Report_Name = "MyLetter"
ReportDest = 2 'print preview
DoCmd.OpenReport Report_Name, ReportDest
MaximizeReportWindow Report_Name

I would like to pass the recordsource and the input
parameters in the code, not in the properties, because I
don't know what they are until I come to run the report.
Is there any way that I can do this.

Any help would be greatly appreciated.

Geri
 
Gen:

1.) Normally, you'd have your report's input parameters property point to a
form where the parameters can be entered by the user. This is how the
sales by year example works in the Northwind Traders database.

2.) If you are using Access 2002 and greater, (won't work in A2K), then you
can set the parameters in the On open event of the report by doing something
like the following which uses the SQL Exec command on a stored procedure:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

When using that methodology, you can pull the parameters using a custom
function from a separate module, where your other, calling code might set
the parameters you want to use into a module level variable.
 
Back
Top