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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

3728

Programmer
Feb 25, 2002
17
SG
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
 
DoCmd.OutputTo acOutputReport, "ReportName", "Snapshot Format (*.snp)", "C:\ReportName.snp"

should do it.

HTH

Ben
 
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?
 
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
 
Hi
Thanks a lot. Could i have a sample code? thanks!!
 
here's my email address
B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------
 
On it's way gary.
3728, what's your address so I can send it to you? ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------
 
my address is

leechkoh@yahoo.com
 
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
 
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
----------------------------------
 
Hi
I have received your mail. thanks.
how to run? to see the results/effect? thanks
 
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
----------------------------------
 
Thanks!
is solved!!!
thank you very much!!
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top