Report with variable name

[KickPuncher]

This problem involves reports, queries, and vba, so hopefully someone can help me with all three.

I have three reports that I run through a macro which saves all three to a specified place on my network. These all previously queried a table and were conditioned on a field which was binary. The reports previously only picked out those records which had a 1. I'm changing this field to an integer type field (for now it'll just have 0, 1, and 2, but eventually more). Now, I'm specifying in my query whether to run it for 1 or 2. My problem is that I need to somehow have the file name of the report show whether I ran it for a 1 or a 2 (so previously the report would simply be named 'MyReport' if I ran it, but now, depending on what parameter I specify, it'll now be named 'MyReport1' or 'MyReport2'). How do I pass the parameter in my query through to the macro so I can append it to the filename? Is it possible that, when I run the macro, I can somehow pass that through to the query instead? Thanks in advance.

[ssanfu]

This problem involves reports, queries, and vba, so hopefully someone can help me with all three.

I have three reports that I run through a macro which saves all three to a specified place on my network. These all previously queried a table and were conditioned on a field which was binary. The reports previously only picked out those records which had a 1. I'm changing this field to an integer type field (for now it'll just have 0, 1, and 2, but eventually more). Now, I'm specifying in my query whether to run it for 1 or 2. My problem is that I need to somehow have the file name of the report show whether I ran it for a 1 or a 2 (so previously the report would simply be named 'MyReport' if I ran it, but now, depending on what parameter I specify, it'll now be named 'MyReport1' or 'MyReport2'). How do I pass the parameter in my query through to the macro so I can append it to the filename? Is it possible that, when I run the macro, I can somehow pass that through to the query instead? Thanks in advance.

OK, you've told us what you want; we also need details. What do you have, what have you tried?

I don't know if you can do what you want using a macro. I am confident it can be done with code.

What do you have now?
How are you changing the parameters for the query?
How are you saving the three reports now?
What have you tried?

[KickPuncher]

OK, you've told us what you want; we also need details. What do you have, what have you tried?

I don't know if you can do what you want using a macro. I am confident it can be done with code.

What do you have now?
How are you changing the parameters for the query?
How are you saving the three reports now?
What have you tried?

Here's what I have now:
I have a database passed to me from a previous user. As part of it, there are three reports called from vba through a macro and ultimately saved as a pdf. All three of these reports query different fields from a table, though they all condition on this binary field and only pull records with a 1. This '1' is hard coded in the query.

Changing parameters:
At first I figured I could simply change the hard coded '1' in the query to [Input Value] to input which parameter (1 or 2) for which they wanted the report run (not through a form). That's where I am so far.

What I've tried:
The above. I'm enough of a novice with Access and VBA to be dangerous, but my knowledge is very spotty; I'm learning as I go.

Hopefully this answers your questions; thanks again for any advice.

[ssanfu]

..., there are three reports called from vba through a macro and ultimately saved as a pdf.

Would you post the VBA?

[KickPuncher]

I'll post it when I get in to work tomorrow morning. Thanks for the help.

[KickPuncher]

Here's the code I have so far:

Function Report_Test()
On Error GoTo Report_Test_Err
Dim FileName As String, ReportName(2) As String, i As Integer
 
ReportName(0) = "Report_A"
ReportName(1) = "Report_B"
ReportName(2) = "Report_C"
 
For i = 0 To 2
    FileName = "c:\Documents and Settings\username\Desktop\" & ReportName(i) & ".pdf"
    DoCmd.OutputTo acOutputReport, ReportName(i), acFormatPDF, FileName
Next i
 
Report_Test_Exit:
    Exit Function
Report_Test_Err:
    MsgBox Error$
    Resume Report_Test_Exit
End Function

[ssanfu]

I'm still not clear on how you call the report. It sounds like you run a macro to call a function (vba) to run the report. How do you run the macro? a button?

Attached is a small A2K mdb file of how I have my reports. In my mdb, I also have buttons to filter the list box to group the reports by function: Audit reports, Employee info reports, Reports by year, etc.

[KickPuncher]

Yes, I just run the macro through the Macros menu/tab directly out of Access, not through a form.

[ssanfu]

Yes, I just run the macro through the Macros menu/tab directly out of Access, not through a form.

Is the mdb for you only, or do others use it?

[KickPuncher]

For myself; I'm not worried much about controls.

[ssanfu]

For myself; I'm not worried much about controls.

I don't use macros, even in my personal mdb's.

But in looking at the macro actions, the only way I can see to get the report name is to set up a macro for each report, that then calls a function that saves the report. (IMO - Macros are very limiting) You will have to add/modify the macros/functions each time you add a report.

one macro calls one function for one report

Function ReportA()

   On Error GoTo ReportA_Err

   Dim FileName As String
   FileName = "c:\Documents and Settings\username\Desktop\ReportA.pdf"
   DoCmd.OutputTo acOutputReport, ReportName(i), acFormatPDF, FileName

ReportA_Exit:
   Exit Function

ReportA_Err:
   MsgBox Error$
   Resume ReportA_Exit

End Function

Function ReportB()
   On Error GoTo ReportB_Err

   Dim FileName As String
   FileName = "c:\Documents and Settings\username\Desktop\ReportB.pdf"
   DoCmd.OutputTo acOutputReport, ReportName(i), acFormatPDF, FileName

ReportB_Exit:
   Exit Function

ReportB_Err:
   MsgBox Error$
   Resume ReportB_Exit

End Function

Sorry I couldn't help you more.......

[KickPuncher]

Perhaps I'm being unclear with where I'm stuck. The code I posted earlier works fine for what I've been working with up to this point. Each of those reports pulls data from the following:

SELECT qryTable1.*
FROM qryTable1
WHERE (((qryTable1.Binary_Condition)=1));

Binary_Condition, going forward, isn't going to be binary anymore; it'll be a field which can hold any number of integers. Right now I can sort of get this to work with:

SELECT qryTable1.*
FROM qryTable1
WHERE (((qryTable1.Integer_Condition)=[Enter Integer]));

The reports then query qryTable1, grab the correct records, and summarize those however they need to on the reports. I have to do some manual manipulation of the saved filename; otherwise, if I run the report multiple times for different integers they'll all be named the same thing. What I'm looking for is some way to pass [Enter Integer] through to my function which calls the reports so I can use that to specify, in the filename, which integer was used. It'd also be nifty if I could use that same [Enter Integer] in my report header.

Am I making any sense? Thanks for sticking with me.

[ssanfu]

Am I making any sense? Thanks for sticking with me.

Yes, but don't know of any way to do what you want using macros.

I have a meeting to go to.... Be back this afternoon.

[KickPuncher]

I'm back with a slightly different problem (I solved the above by creating a global variable which I referenced in a function returning only that value, letting me tweak the query parameter and report name at the same time).

Anywho, I'm creating two options for these reports I'm running: either saving it using DoCmd.OutputTo to a predefined directory which changes monthly, or opening it using DoCmd.OpenReport. In both cases, I'm hoping to take the report name and modify it based on that global variable. I have the OutputTo portion of the code working fine. It looks like this:

FileName = "c:\Documents and Settings\username\Desktop\" & ReportName(i) & " " & Global_Variable & ".pdf"
DoCmd.OutputTo acOutputReport, ReportName(i), acFormatPDF, FileName

ReportName(i) is a string array holding different report names.

However, I'm having problems with the OpenReport portion. Here's what I have:

DoCmd.OpenReport ReportName(i), acViewNormal

Is there any way to change the file name that Access/Windows populates when it opens the SaveAs dialog box when I run that piece of code? Right now it's only returning the unmodified name of the report "ReportName(i)" when I'd like something like "Reportname(i) & Global_Variable", and I don't see anything in the options for OpenReport like I do in OutputTo. Thanks again.