none
How to get all properties of a report in Access?

    Question

  • hello all dears,

    I try to get all properties of a report by VBA code with define an AccessObjectProperty property and a loop(example:For Each prp In MyAccessObject.Properties) but I can not get any property with this way.How can I do it?

    • Edited by Jafar88 Tuesday, October 19, 2010 7:05 AM
    Monday, October 18, 2010 12:06 PM

Answers

  • Hi Jafar,

    here is a piece of code:

    Public Function RepPrt() As Integer
    Dim i%
      For i = 0 To Reports("test").Properties.Count - 1
        Debug.Print Reports("test").Properties(i).Name & " = " & Reports("test").Properties(i).Value
      Next i
      RepPrt = Reports("test").Properties.Count
    End Function
    
    

     

    and here is the result of Immediate window:

    ? repprt()
    RecordSource = test
    Filter =
    FilterOn = False
    OrderBy =
    OrderByOn = False
    Caption = test
    RecordLocks = 0
    AutoResize = True
    AutoCenter = False
    PageHeader = 0
    PageFooter = 0
    DateGrouping = 1
    GrpKeepTogether = 1
    PopUp = False
    Modal = False
    BorderStyle = 2
    ControlBox = True
    MinButton = True
    MaxButton = True
    MinMaxButtons = 3
    CloseButton = True
    Width = 9026
    Picture = (none)
    PictureType = 0
    PictureSizeMode = 0
    PictureAlignment = 2
    PictureTiling = False
    PicturePages = 0
    MenuBar =
    Toolbar =
    ShortcutMenuBar =
    GridX = 10
    GridY = 10
    LayoutForPrint = True
    FastLaserPrinting = True
    HelpFile =
    HelpContextId = 0
    Hwnd = 6687528
    Count = 10
    Page = 1
    Pages = 1
    LogicalPageWidth = 9026
    LogicalPageHeight = 13958
    ZoomControl = 0
    HasData = -1
    Left = 1440
    Top = 14885
    Height = 513
    PrintSection = True
    NextRecord = True
    MoveLayout = True
    FormatCount = 1
    PrintCount = 1
    Visible = True
    Painting = True
    PrtMip = ? ? ? ?   ? N   U   ?  
    PrtDevMode =                 ¨???#     ??d?? ?                            ??        ??"D? ??                                                                                                                                                                                                                                                                                       D ??  AMicrosoft XPS Document Writer ?????????E??????????????g?F????e???d?????n???????????????n???1????e???????????                    ??a
    PrtDevNames = &D                              ????
    Name = test
    PaletteSource = (Default)
    Tag =
    PaintPalette =
    OpenArgs =
    OnOpen =
    OnClose =
    OnActivate =
    OnDeactivate =
    OnNoData =
    OnPage =
    OnError =
    Dirty = False
    WindowWidth = 8070
    WindowHeight = 11550
    CurrentRecord = 3
    PictureData =
    PicturePalette =
    HasModule = False
    acHiddenCurrentPage = 2
    Orientation = 0
    WindowTop = 1290
    WindowLeft = 930
    Moveable = True
     82

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, October 18, 2010 12:56 PM

All replies

  • Hi Jafar,

    here is a piece of code:

    Public Function RepPrt() As Integer
    Dim i%
      For i = 0 To Reports("test").Properties.Count - 1
        Debug.Print Reports("test").Properties(i).Name & " = " & Reports("test").Properties(i).Value
      Next i
      RepPrt = Reports("test").Properties.Count
    End Function
    
    

     

    and here is the result of Immediate window:

    ? repprt()
    RecordSource = test
    Filter =
    FilterOn = False
    OrderBy =
    OrderByOn = False
    Caption = test
    RecordLocks = 0
    AutoResize = True
    AutoCenter = False
    PageHeader = 0
    PageFooter = 0
    DateGrouping = 1
    GrpKeepTogether = 1
    PopUp = False
    Modal = False
    BorderStyle = 2
    ControlBox = True
    MinButton = True
    MaxButton = True
    MinMaxButtons = 3
    CloseButton = True
    Width = 9026
    Picture = (none)
    PictureType = 0
    PictureSizeMode = 0
    PictureAlignment = 2
    PictureTiling = False
    PicturePages = 0
    MenuBar =
    Toolbar =
    ShortcutMenuBar =
    GridX = 10
    GridY = 10
    LayoutForPrint = True
    FastLaserPrinting = True
    HelpFile =
    HelpContextId = 0
    Hwnd = 6687528
    Count = 10
    Page = 1
    Pages = 1
    LogicalPageWidth = 9026
    LogicalPageHeight = 13958
    ZoomControl = 0
    HasData = -1
    Left = 1440
    Top = 14885
    Height = 513
    PrintSection = True
    NextRecord = True
    MoveLayout = True
    FormatCount = 1
    PrintCount = 1
    Visible = True
    Painting = True
    PrtMip = ? ? ? ?   ? N   U   ?  
    PrtDevMode =                 ¨???#     ??d?? ?                            ??        ??"D? ??                                                                                                                                                                                                                                                                                       D ??  AMicrosoft XPS Document Writer ?????????E??????????????g?F????e???d?????n???????????????n???1????e???????????                    ??a
    PrtDevNames = &D                              ????
    Name = test
    PaletteSource = (Default)
    Tag =
    PaintPalette =
    OpenArgs =
    OnOpen =
    OnClose =
    OnActivate =
    OnDeactivate =
    OnNoData =
    OnPage =
    OnError =
    Dirty = False
    WindowWidth = 8070
    WindowHeight = 11550
    CurrentRecord = 3
    PictureData =
    PicturePalette =
    HasModule = False
    acHiddenCurrentPage = 2
    Orientation = 0
    WindowTop = 1290
    WindowLeft = 930
    Moveable = True
     82

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, October 18, 2010 12:56 PM
  • Thanks Andrey,

    I applied this code to my report name("rprt_test1"),but i get error '2451' (misspelled report name or object is closed or isn't exist). I wonder that why don't our codes work correct? my code is here:

    Function GetReportPrps()

    Dim accObj as AccessOject , db as Object

    Dim prp As AccessObjectProperty

    Set db = Application.CodeProject

    Set accObj=db.AllReports("MyReportName")
    For Each prp In accObj.Properties

        On Error Resume Next

        Debug.Print prp.Name & ";" & prp.Value

    Next prp

    Set db = NoThing

    Set accObj = NoThing

    Set prp = NoThing

    End Function

    Please help me,thanks everybody.

    Tuesday, October 19, 2010 4:49 AM
  • Jafar,

    The main difference between your code and Andrey's code is that you are "properly" using early binding and he is not. Since he isn't using early binding, his property.value references get resolved through an entirely-late-bound variant datatype, where yours are getting resolved differently, and somewhere in there is a key difference in how certain .value propery datatypes are able to be resolved, where others are not.

    Try to not use early binding, or change your variable definition for prp to a variant.

    (and yes, I know this goes against standard "best practices" recommendations which prefer early over late binding).


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Tuesday, October 19, 2010 1:06 PM
  • Thank Mark,but by the MS Access Help, for getting any a report object property such as "Description" property we can use this way, now, how can I use late binding?My ploblem is that I can't get "Caption" and "Description" properties of reports by Andrey's code.
    Wednesday, October 20, 2010 5:42 AM
  • HI Jafar,

    unfortunately, you must have your report being opened to use the code I've proposed. You can see it in your error description ("object is closed").

    And what do you finally want to do with report's caption, for example? What is the purpose of these actions? Maybe we can find some other solutions.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, October 20, 2010 5:52 AM
  • Hi Andery, Yes,I know that , but even when my report is opened,the code can't get the report properties .I want to list all report captions or description in a ListBox control,I do it by reports name but I couldn't list them by captions.
    Wednesday, October 20, 2010 8:54 AM
  • Hi Jafar,

    if it doesn't matter for you that you have to open a report to get its .Caption, I can offer you the next piece of code:

    Private Sub Command0_Click()
    Me.List1.RowSourceType = "Value List"
    Me.List1.RowSource = ""
    Dim rpt As AccessObject
      For Each rpt In CurrentProject.AllReports
        DoCmd.OpenReport rpt.Name, acViewPreview, , , acHidden
        Me.List1.AddItem Reports(rpt.Name).Caption
        DoCmd.Close acReport, rpt.Name, acSaveNo
      Next rpt
    End Sub
    
    

    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, October 20, 2010 10:00 AM
  • I think the confusion here is that you are trying to get the properties of a Report object, but your code is using an AccessObject.

    This is essentially the same thing Andrey used in his first reply, except this opens the report instead of relying on it already being open.

    Dim db as Database
    Dim rpt As Report
    Dim prp As Property

    Set db = CurrentDb()
    DoCmd.OpenReport "MyReportName", acDesign
    Set rpt = Reports("MyReportName")
    For Each prp In rpt.Properties

        On Error Resume Next

        Debug.Print prp.Name & ";" & prp.Value

    Next prp

    Wednesday, October 20, 2010 11:50 AM
  • Hi Marshall,

    so, am I right that we can't get any .Property() of any report while it is closed? And there is no difference between

    Dim rpt As AccessObject
     For Each rpt In CurrentProject.AllReports
      DoCmd.OpenReport rpt.Name, acViewPreview, , , acHidden
      Me.List1.AddItem Reports(rpt.Name).Caption
      DoCmd.Close acReport, rpt.Name, acSaveNo
     Next rpt
    
    

    and

    Dim i%
     For i = 0 to Reports.Count - 1
      DoCmd.OpenReport Reports(i).Name, acViewPreview, , , acHidden
      Me.List1.AddItem Reports(i).Caption
      DoCmd.Close acReport, Reports(i).Name, acSaveNo
     Next i
    
    

    if we want to get the .Caption property of every existing report?

    For example, I have some reports which are being opened for a long time and have parameters, so, if I want to get all their captions, it'll take me some time. It's not a good solution.

    The only solutions I see here is either to have .Caption and .Name to be equal to each other or to have a table with 'user-friendly' reports' names (or hard-coded list of them).


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, October 20, 2010 12:17 PM
  • Yes, you cannot get the properties of a report (or form) unless the report (or form) is open.

    No, the two code samples are not equivalent: the Reports collection only contains those reports that are open, so Reports.Count is not the count of all the reports in the application.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Wednesday, October 20, 2010 12:51 PM
  • Thanks Douglas!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, October 20, 2010 1:01 PM
  • That's right. A report must be open to get at its properties.

    Those two blocks of code are not at all the same.  The Reports collection only contains open reports.  That makes the DoCmd.OpenReport line in the second block redundant because those reports are already open, which is not what you want.

    You can use the AllReports collection to find all the reports in your db, but you must then open each report to get its properties, as in your first block.  There is another way to get to all of the reports via the Documents collection in the Reports Container, but I think AllReports is a little easier.

    There is no need to make the name and Caption the same.  You can create a table (named ReportCaptions) with two fields, one for the report name and the other for the report caption.  Then populate the table using code something like your first block:

    Dim db As Database
    Fom rs As Recordset
    Dim rpt As AccessObject
     Set db = CurrentDb()
    db.Execute "DELETE * FROM ReportCaptions"
    Set rs = db.OpenRecordset("ReportCaptions", dbOpenDynaset)
    For Each rpt In CurrentProject.AllReports DoCmd.OpenReport rpt.Name, acDesign, , , acHidden With Reports(rpt.Name)
    rs.AddNew
    rs!ReportName = .Name
    rs!ReportCaption = .Caption rs.Update
    End With
    DoCmd.Close acReport, rpt.Name, acSaveNo Next rpt
     rs.Close : Set rs = Nothing
    Set db = Nothing
    Wednesday, October 20, 2010 3:55 PM