How to write a marco that output a report in snapshot format 2

[3728]

When i run the report manually, the report will prompt the user to input some value and later it runs.

How do i write a marco that will automate the process of accepting the input agruement to the report and output it into a snapshot format?

thanks

 

[oharab]

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

should do it.

HTH

Ben

 

[3728]

However, how do you code such that it can accept an input variable from the marco and pass it to the report and then output as snapshot format?

 

[oharab]

Ok then:


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!

Example code is available, just let me know.

Ben

 

[3728]

Hi
Thanks a lot. Could i have a sample code? thanks!!

 

[oharab]

here's my email address
B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[oharab]

On it's way gary.
3728, what's your address so I can send it to you? ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[3728]

my address is

leechkoh@yahoo.com

 

[3728]

Hi
I tried to do it. When i run it,
it hit this error.
"Run-time error '3464'
Data type mismatch in criteria expression"


When I press "debug"
It highlighted this line of code to me
"DoCmd.OutputTo acOutputReport "Report1", "Snapshot Format (*.snp)", C:\temp\Report1.snp"

Do you have any idea what happen?

Thanks

 

[oharab]

What service packs do you have installed?
It works fine on my Access 97 with SR 2 installed.

I think you will need at least SR1

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[3728]

Hi
I have received your mail. thanks.
how to run? to see the results/effect? thanks

 

[oharab]

Open the database and open the module mdlOutputInCode, then run the Sub DoIt().
Like I said, make sure you are up to date with the service packs.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[3728]

Thanks!
is solved!!!
thank you very much!!

 

[boschg]

I tried the code of Ben.
Now I have the following problem:
When I add the call to GetParam() to the query, I’m getting an error:
ADO Error: ‘GetParam’ is not a recognized function name

How can I tell Access where to find the function??

I am running Access 2002 (10.4302.4219) SP-2. The Database is MS SQL 2000 Server.

Thanks for your support.