Automatically open all combos of select cases for report criteria

[pbrown77]

To make the FE smaller, I reduced the number of reports by using select cases Case1 = 5 options and Case2 = 2 options. However, now it has been determined that we need to save, "electronically" a copy of each possible combination to a server folder.

Before using the cases, it was simple.... there was a report for each option and all I had to do was write code or used a macro to save the report to the server.

Is there any type of code that I could write, or macro, that would:
1. Run each option
2. Save each option to the server with a differnt file name (so they would not simply overwrite)

I could always open each report one at a time and save as, but that would take all day since there are approx 24 reports.

Case 1
Option 1 = direct manpower
2 = semdirect
3 = maint
4 = Pre
5 = all manpower

Case 2
Option 1 = TN
2 = MS

 

[Remou]

Hi
I think you could use OutputTo. For example, set up your report lists in a table or array and cycle through them. Use today's date or date and time for the file name. Is this the kind of thing you were thinking of?

 

[pbrown77]

Output to is exactly what I was thinking of.
What is this "table or array and cycle through them"?

Could you help or do you know of some place that could help in this set up? (from a basic start)

 

[Remou]

What I was thinking of is a table containing a list of your report names. You could loop through a recordset with Do ... Loop:

Set Rs = CurrentDb.OpenRecordset("Select * From tblReports Where SomeField=Something")
Do While Not Rs.EOF()
    MyFile = Rs!ReportName & Format(Date, "dd") & ".rtf"
    DoCmd.OutputTo acOutputReport, Rs!ReportName, acFormatRTF, MyFile
    Rs.MoveNext
Loop

I have not tested the above snippet, so I hope I have got the syntax right.
Or ... the example below should ouput all reports starting with Report:

For Each rpt In CurrentProject.AllReports
    If rpt.Name Like "Report*" Then
        MyFile = rpt.Name & Format(Date, "ddmmyy") & ".rtf"
        DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, MyFile
    End If
Next

There could be a few disadvantages to the above method, such as using names to control code is probably not a good idea in the longer term.

 

[pbrown77]

Here is the problem.
There is 1 report per item with several reports.
(1 report for summary, 1 report by day, 1 report by month, etc)
The users select criteria from the cases that depict how they want the report to run then select the report and it opens.
Therefore, I do not have a list of reports since there are simply different versions of the same report and that is what I need. To download every possible version of a report.
I.E.
Run report 1 with Direct / TN
Semi / TN
Main /TN
PR / TN
Direct / MS
Semi / MS
Main / MS
PR / MS
then do the same for the remaining reports.

 

[Remou]

Ok, missed what you are doing. Where I an getting confused is when you say "download every possible version of a report" and "users select criteria from the cases". Do you wish to save every possible version as samples, or do you wish to save these reports after the user has selected criteria? Perhaps if you included a little code for what is happening at the moment, it might help.

 

[Remou]

PS I'm not sure whether or not this post might be better in the reports forum.

 

[pbrown77]

FYI...
I was able to open the reports as needed by using the below code (might help if anyone else tries something like this...)

Private Sub Command69_Click()
Dim strWhere As String
Dim strwhere1 As String
Dim strwhere2 As String
Dim mdofreport As String
Dim dirDate1 As String
Dim dirfac As String
Dim dirmp As String
Dim intLoc As Integer
Dim intLab As Integer

'3 variable array (starts at 0)
Dim strLocation(2) As String

'5 variable array (starts at 0)
Dim strLabor(4) As String


strLocation(0) = "([CC Summary 2nd Level] = 1340) OR " _
& "([CC Summary 2nd Level] > 3999 AND " _
& "[CC Summary 2nd Level] <> 9500 AND " _
& "[CC Summary 2nd Level] Not Between 7700 And 7799)"

strLocation(1) = "([CC Summary 2nd Level] In (1350, 1800, 1801, 1805, 1905, 9500)) OR " _
& "([CC Summary 2nd Level] Between 3000 and 3999) OR " _
& "([CC Summary 2nd Level] Between 7700 and 7799)"

strLocation(2) = "([CC Summary 2nd Level] In (1340, 1350, 1800, 1801, 1805, 1905, 9500)) OR " _
& "([CC Summary 2nd Level] Between 3000 and 7999)"

strLabor(0) = "([Labor Code] In ('D', 'P', 'K', 'H', 'J', 'E', 'N'))"
strLabor(1) = "([Labor Code] In ('G', 'Q'))"
strLabor(2) = "([Labor Code] In ('R', 'L', 'O', 'F'))"
strLabor(3) = "([Labor Code] In ('V', 'Y', 'X'))"
strLabor(4) = "([Labor Code] In ('D', 'P', 'K', 'H', 'J', 'E', 'N', 'G', 'Q', 'R', 'L', 'O', 'F', 'V', 'Y', 'X'))"

For intLoc = 0 To 2

Select Case intLoc

Case 0
gstrRTitle = "Smyrna - Decherd"
Case 1
gstrRTitle = "Canton"
Case 2
gstrRTitle = "Smyrna - Decherd - Canton"



End Select

For intLab = 0 To 4

Select Case intLab

Case 0
gstrTitle = "Direct Manpower"
Case 1
gstrTitle = "SemiDirect Manpower"
Case 2
gstrTitle = "Maintenace Manpower"
Case 3
gstrTitle = "PreAct Manpower"
Case 4
gstrTitle = "All Manpower"

End Select

strWhere = "(" & strLabor(intLab) & ") AND (" & strLocation(intLoc) & ")"
Dim myDir1
Dim myDir2
Dim mydir3



'*** The below "if" code is writen for each report ***
If DCount("*", "Query for All Reports", strWhere) <> 0 Then
DoCmd.OpenReport "A - Attendance Summary by Plant", acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, "A - Attendance Summary by Plant", acFormatSNP, "I:\Paul\Monthly Attendance Reports\" & dirDate1 & "\" & dirfac & "\" & dirmp & "\Attendance Summary by Plant" & " " & gstrRTitle & " " & gstrTitle & ".snp"

End If


Next intLab

Next intLoc