Array for report names

[Jaye7]

I have the following script which I use to modify all report settings, can someone please help with an array so that I can easily list reports that I want to change the settings for, there could be 50+ reports.

I can use a string but I have to put str1 as string, str2 as string etc... whereas an array would be easier (if I knew how to do it).

Public Sub ModifyAllReportsProperties()
      
        Dim obj As AccessObject, dbs As Object
      Set dbs = Application.CurrentProject
       Dim ReportName As String
   
  ‘Array something like ***********************************************************
   
  Dim MyArray as Array
   
  Array1 = “Report1”
  Array2 = “Report2”
  Array3 = “Report3”
  ‘many more arrays
   
  ‘**************************************************************************************
   
       
      ' Search for open AccessObject objects in AllReports collection.
      
      'For Each obj In dbs.AllReports
      
      For Each obj In dbs.AllReports
              
              DoCmd.OpenReport obj.Name, acDesign
              ReportName = obj.Name
                                      
          Reports(ReportName).PopUp = False
          Reports(ReportName).Modal = False
          Reports(ReportName).AutoCenter = True
          Reports(ReportName).AutoResize = True
          Reports(ReportName).FitToPage = True 'fit to page for reports
          Reports(ReportName).BorderStyle = 3 ' 0=none, 1 = thin, 2 = default, 2 = sizable, 3 = dialog, 4=dots, 5=sparse dots, 6=dash dots, 7=dash dot dot, 8=double dolid
          Reports(ReportName).ControlBox = True
          Reports(ReportName).CloseButton = True
          Reports(ReportName).MinMaxButtons = yes
          Reports(ReportName).Moveable = False        
          Reports(ReportName).ShortcutMenuBar = "" 'cmdRightClick2
          Reports(ReportName).RibbonName = "" 'ReportPrint           
             DoCmd.Close acReport, ReportName, acSaveYes
      Next obj
      MsgBox "Finished"
  End Sub

 

[plog]

First, this code is in an Access database, correct? Why not store your data in a structure inherent to Access that can easily be added to, modified and updated? Why not just use a table?

Second: http://lmgtfy.com/?q=VBA+Array+tutorial

 

[pbaldy]

+1 for a table. I rarely use arrays, and I got a chuckle out of "whereas an array would be easier (if I knew how to do it)".

 

[jdraw]

I agree with plog and Paul -- no need for array

Here's some code that loops through all Reports in the database and identifies the recordsource of each. I think you could adapt the loop section to do what you want.

Sub ListReportRecordSources()

' List the recordsources of all reports.


    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim doc As DAO.Document

    Dim lngReportCount As Long

    Debug.Print "*** Beginning scan of Reports for Record Source field "

    Set db = CurrentDb

''''' L o o P  starts here +++++++++++++++++++++++++++++

    For Each doc In db.Containers("Reports").Documents

        DoCmd.OpenReport doc.name, acDesign, WindowMode:=acHidden

        With Reports(doc.name)

            lngReportCount = lngReportCount + 1

           ' Debug.Print "Report " & .name & " RecordSource: " & .RecordSource

 ' +++++++++++  Put you code here +++++++++++++++

            DoCmd.Close acReport, .name
        End With
    Next doc
''''' Lo o P  Ends here  +++++++++++++++++++++++++++++++

Exit_Point:
    Set doc = Nothing
    Set db = Nothing

    Debug.Print "*** Scanned " & lngReportCount & " reports."
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error " & Err.number
    Resume Exit_Point

End Sub

 

[Jaye7]

Thanks everyone,
The reason that I wanted an array is that it is something that will be done just once and then it will not be used for those reports again, then I have just 5 reports or 7 etc... and I can then just paste the names into the string and run it, I would prefer not to have another table that would be empty most of the time.

 

[jdraw]

I suggest you copy each of the reports -- a backup Just in case.
There is no table, nor array, in the code I posted.

But make sure you're working with reports that can be deleted/edited etc until you get it working. Then, using your backup, copy the reports to be changed and run them thru the sub.

Backup First

 

[MarkK]

One quick way to make an array of strings is . . .

dim vArray
dim var
vArray = split("Report1 rptInvoice myBestReport rrrPiratesWereHererrrr rLastReport")

for each var in vArray
   debug.print var
next

. . . or a little more verbose . . .

dim vArray
vArray = Array("Report1", "rptInvoice", "myBestReport", "rrrPiratesWereHererrrr", "rLastReport")

Or, if you want to pull a few items out of a collection where the items may or may not be present . . .

dim ao as accessobject
for each ao in currentproject.allreports
   select case ao.name
      case "Report1", "rptInvoice", "myBestReport", "rrrPiratesWereHererrrr", "rLastReport"
[COLOR="Green"]         'doesn't fail if one of the list items is not present in the collection
         'only runs for items that are present[/COLOR]
         myReportEditor ao.name
      case else
[COLOR="Green"]         'ignore everything else[/COLOR]
   end select
next

 

[Jaye7]

Thanks Mark and thanks everyone else for your suggestions.

I was going to use the following.

Public Sub ModifyAllReportsPropertiesTest()
    
      Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
     Dim ReportName As String
    
    For Each obj In dbs.AllReports
    If obj.Name = "Report1" Or obj.Name = "Report2" Then
            
            DoCmd.OpenReport obj.Name, acDesign
            ReportName = obj.Name
        
            Reports(ReportName).Modal = True
           
           DoCmd.Close acReport, ReportName, acSaveYes
        End If
    Next obj
    MsgBox "Finished"
End Sub

But yours is easier to add names as you don't have to keep adding the or statement. That's why I wanted an array, I have seen them used before but weren't capable of applying it myself.

Sub ModifyAllReportsPropertiesTestArray()

Dim ao As AccessObject
For Each ao In CurrentProject.AllReports

 Select Case ao.Name
 
      Case "Report1", "Report2"
         'doesn't fail if one of the list items is not present in the collection
         'only runs for items that are present
    
         DoCmd.OpenReport ao.Name, acDesign
         Reports(ao.Name).Modal = True
         DoCmd.Close acReport, ao.Name, acSaveYes
         
      Case Else
         'ignore everything else
   End Select
Next
End Sub

 

[MarkK]

If you put this in a subroutine . . .

Sub MakeReportModal(objName as string)
   DoCmd.OpenReport objName, acDesign
   Reports(objName).Modal = True
   DoCmd.Close acReport, objName, acSaveYes
End Sub

. . . then you have a framework for doing updates where you can you can snap-in/out your object list, AND the update functionality--AND the collection, for that matter.

dim ao as accessobject
for each ao in [COLOR="DarkRed"]currentproject.allreports[/COLOR]
   select case ao.name
      case [COLOR="DarkRed"]"Report1", "rptInvoice", "myBestReport", "rrrPiratesWereHererrrr", "rLastReport"[/COLOR]
         [COLOR="DarkRed"]FunctionCall[/COLOR] ao.name
   end select
next

In red are the parts you should be able to snap-in/snap-out for the best flexibility.

 

[Jaye7]

That's awesome. I can add that to my custom rightclick menu so that I can process updates whenever I am in any form or report on the fly.