Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

Parameter Reports In Code

How do I run a parameter report/query from code? by oharab
Posted: 24 May 02 (Edited 28 May 02)

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:
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:

If you want help with this, or have any comments, you can email me: benohara@btopenworld.com

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close