Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Picking up a report's description in VBA code (2K)

    Does anyone know of a way I can get VBA to pick up the descriptions I have entered manually in my report properties through the database window?

    I wish to create a combo field in a dialog box, which will enable the user to view a sorted list of reports and select one to open and/or print. I have a couple of hundred reports whose descriptions have been carefully created for sorting and clarification purposes. I need to loop through the list of reports and store in an array the names and descriptions of any that are not hidden. This array will then be sorted for use in the combo list.

    If I use the AllReports collection, I can access very little information without opening the reports; for example, I can get the name, but cannot check the Visible or Tag properties.

    If I loop through AllReports and open each report in turn, I can access the Visible property and other properties such as Tag, but do not seem to be able to access the report description in any way. I also find that, even with Echo off, there is a lot of flicker as it opens, reads, then closes each report to build the list.

    So far, my best solutions are to:

    (a) Create a table with fields for the report name and description (this would allow me to create additional fields to assist selection, but would be very time-consuming, and require careful maintenance every time a report is added, modified or deleted); or

    ([img]/forums/images/smilies/cool.gif[/img] Manually copy the Descriptions to the Tag fields, then loop through AllReports to open each report and put the name and Tag property into the array (which would also be time-consuming and require maintenance as for (a), as well as having the flicker problem).

    Perhaps I am missing something - I have looked through the enormous list that is displayed when I click the More Controls button on the Toolbox, but nothing stands out as an obvious way of enabling the user to browse a list of reports.

    I have searched back over a year of posts and don't believe this question has already been raised, but apologise if it has been and I have just missed it. I'm sure there are brighter minds out there than mine, so hope someone can make sense of my question and point the way to a solution!

    Thanks and regards

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking up a report's description in VBA code

    Here's what you need (note: you don't need to open the reports):
    <font face="Georgia">
    Public Sub GetReportDescriptions()

    Dim objCurrentProject As Object
    Dim objAllReports As AllReports
    Dim objReport As AccessObject
    Dim strReportDescription As String

    Set objCurrentProject = Application.CurrentProject
    Set objAllReports = objCurrentProject.AllReports

    For Each objReport In objAllReports
    strReportDescription = CurrentDb.Containers("Reports").Documents(objRepor t.Name).Properties("Description")
    Debug.Print strReportDescription
    Next objReport

    End Sub
    </font face=georgia>
    You may be able to simplify this further by simply stepping through the Containers('Reports").Documents collection.

    For those situations where you do need to open the report to look at other properties, you can avoid the "flickering" by specifying acHidden in the DoCmd.OpenReport statement.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Picking up a report's description in VBA code

    Thank you... thank you... thank you!

    I don't know which impresses me more - the simplicity of the answer or the speed of your response! Either way, many thanks - the code you supplied is just what I need, and the extra hint about acHidden is something I will store away for next time I need it!

    Cheers

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking up a report's description in VBA code

    Since you're using the DAO Containers object anyway, you might as well use DAO throughout:

    Public Sub GetReportDescriptions()
    Dim dbs As DAO.Database
    Dim doc As DAO.Document
    Dim strReportDescription As String

    Set dbs = CurrentDb
    For Each doc In dbs.Containers("Reports").Documents
    strReportDescription = doc.Properties("Description")
    Debug.Print strReportDescription
    Next doc
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Picking up a report's description in VBA code

    Thanks, Hans, for your suggestion. It seems a succinct and logical extension of Tom's code but.. when I try to run it, it stops on the line "strReportDescription = doc.Properties("Description")" and I get "Run-time Error '3270': Property Not Found.".

    This makes absolutely no sense to me, as Tom's code still runs fine, and a line-by-line comparison seems to indicate that both sets of code do exactly the same thing!

    I have a working solution, so am not asking anyone to spend any more time on this (how do you find the time to make 50,000 posts in four and a half years?!?!), but thought I should let you know what happened, and thank you for your response.

    Cheers

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking up a report's description in VBA code

    Can't explain that - on my PC, both Tom's code and mine bomb if a report doesn't have a description. Here is a version that handles reports without a description:

    Public Sub GetReportDescriptions()
    Dim dbs As DAO.Database
    Dim doc As DAO.Document
    Dim strReportDescription As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    ' Loop through all reports
    For Each doc In dbs.Containers("Reports").Documents
    ' Get description
    strReportDescription = doc.Properties("Description")
    ' Do something with it
    Debug.Print doc.Name & ": " & strReportDescription
    Next doc

    Exit Sub

    ErrHandler:
    If Err = 3270 Then
    ' No Description property - show custom text (modify as desired)
    strReportDescription = "(None)"
    Resume Next
    Else
    ' Display error message and get out
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Picking up a report's description in VBA code

    Aha - I hadn't realised that Tom's procedure was picking up the reports in a different sequence from yours - the first report in Tom's procedure had a description, whereas yours picked up a hidden report with no description first.

    I can actually use this to eliminate the hidden reports, by making sure all the visible reports have a description, but none of the hidden reports (I still have been unable to find a way of getting the visible property without opening the report, so this will serve as a workaround).

    Thanks again for persevering - I was really bugged by what appeared to be an anomaly, but clearly hadn't looked carefully enough for the answer!

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •