Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I run a parameter report/query from code?

Parameter Reports In Code

How do I run a parameter report/query from code?

by  oharab  Posted    (Edited  )
There are several ways of running a parameter query from code without needing to enter a value manually or without getting an error. This is probably the most flexible as it allows you to use Docmd.OutputTo as well as Docmd.OpenReport methods.

Create a new general module, and on the declarations page dimension a variant array. (I've used a 10 element array but it can be any size, the smaller the better to efficiently use memory.) Use a variant array because you need it to hold any type of data.

Dim arrParameter(10)

Create a new Sub Procedure which allows you to set the values of the array such as:
Public Sub SetParam(ByVal InputVal, ByVal ParamID)

arrParameter(ParamID) = InputVal

End Sub
Where the InputVal is the value of the parameter, and ID is the parameter number you will be setting.

Create a similar Function to retreive the value of the parameter from the array:
Public Function GetParam(ByVal ParamID)

GetParam = arrParameter(ParamID)

End Function
Then in your query for the report where you would normally set a parameter in the criteria section simply place a call to the function to get a parameter as in:
=GetParam(X)
Where X = 1 or 2 or 3 and so on for the number of parameters for the query.

Then in your VB code, prior to opening the report, simply call the SetParam subprocedure for each parameter of the report with the values of the parameters you want to the report to use, as in:

Public Sub PrintRpt(BeginDate, EndDate)

Call SetParam(BeginDate, 1)
Call SetParam(EndDate, 2)

DoCmd.OutputTo acOutputReport, "ReportName", "Snapshot Format (*.snp)", "C:\ReportName.snp"

End Sub

Job Done!
For an example Access 97 database:
http://oharab.hypermart.net/files/report.zip

If you want help with this, or have any comments, you can email me: benohara@btopenworld.com
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top