Results 1 to 5 of 5
  1. #1
    DavidMadge
    Guest

    Listbox of Report names

    What is the best way to fille a listbox with the names of all saved reports ? I'm currently doing the following to fill a table on which a query (populating the listbox) is based:

    intReports = CurrentDb.Containers!Reports.Documents.Count
    For intReportNumber = 0 To intReports - 1
    DoCmd.GoToRecord , , acNewRec
    [RepName]=CurrentDb.Containers!Reports(intReportNumber).Nam e
    Next intReportNumber

    This works but seems clumsy. I haven't found how to access the list of report names directly in the query or lisbox. Is that possible ?

    David

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox of Report names

    Report names and descriptions are actually properties of the report objects, so no, there isn't an easy way to access them in a query or listbox. What you have to do is to loop through the collection of report objects and gather the information. Most of them are not radically different from what you're using, although I can't tell from your snippet whether you're appending a new record to a table and assigning the report name to a field in that table or if I misunderstood. Anyhow, here are some alternatives, although there are probably others I am leaving out. Keep in mind that these are fragmentary routines, with what goes before and comes after left out, along with any necessary error handling.


    1. Query the Name field of the MSysObjects table for object of type -32764, either directly or through DAO code. Like the other methods, it will return all reports and subreports in your database and you'll have to filter them out based on a naming convention.

    2. Use DAO to loop through the Reports container and get the name of each report. You would approach that something like the following, which isn't awfully different from what you appear to be doing now except easier to read.

    <pre> Dim dbs As DAO.Database
    Dim ctr As DAO.Container
    Set dbs = CurrentDb()
    Set ctr = dbs.Containers("Reports")
    For intLoop = 0 To ctr.Documents.Count - 1
    'do something here with the report name
    [RepName] = ctr(intLoop).name
    Next intLoop</pre>


    3. Use ADO to loop throught the AllReports collection of the current project and get the names from that collection like this:

    <pre> Dim obj As AccessObject
    Dim dbs As Object
    Set dbs = Application.CurrentProject
    ' loop through AccessObject objects
    ' in the AllReports collection.
    For Each obj In dbs.AllReports
    [RepName]=obj.Name
    Next obj</pre>

    Charlotte

  3. #3
    DavidMadge
    Guest

    Re: Listbox of Report names

    Thanks Charlotte - you understood right. I'm using your DAO loop as it's clearer than mine.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox of Report names

    I have just answered another question like this. For the combo set the source to a function name and use a function somewhat like the following. I would suggest you build a static array with the report names rather than trying to generate each time the combo requires another report name.

    Private Function HandleWeekCalendar(ctl As Control, lID As Long, lRow As Long, lCol As Long, iCode As Integer) As Variant
    Static lDisplayID As Long

    On Error GoTo Err_HandleWeekCalendar
    Select Case iCode
    Case acLBInitialize
    ' Record and return the lngID for this function.
    lDisplayID = Timer
    HandleWeekCalendar = lDisplayID

    Case acLBOpen
    HandleWeekCalendar = lDisplayID

    Case acLBGetRowCount
    ' Return number of weeks in the selected year
    On Error Resume Next
    HandleWeekCalendar = WeekInfo.MaxWeek

    Case acLBGetColumnCount
    ' Return number of fields (columns)
    HandleWeekCalendar = 3

    Case acLBGetColumnWidth
    HandleWeekCalendar = -1 'default width

    Case acLBGetValue
    Select Case lCol
    Case 0
    HandleWeekCalendar = lRow + 1
    Case 1
    HandleWeekCalendar = WeekInfo.WeekStart(lRow + 1)
    Case 2
    HandleWeekCalendar = WeekInfo.WeekFinish(lRow + 1)
    Case Else
    Beep
    ErrorMsgBox "HandleWeekCalendar called with lCol = " & lCol
    End Select

    Case acLBEnd
    lDisplayID = 0

    End Select

    Bye_HandleWeekCalendar:
    Exit Function

    Err_HandleWeekCalendar:
    ErrorMsgBox Err.Description, "HandleWeekCalendar"
    HandleWeekCalendar = False
    Resume Bye_HandleWeekCalendar
    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox of Report names

    Actually, the method I prefer is to create a custom report property called DisplayName and only populate it for reports I want to show in listboxes and comboboxes. That greatly reduces the number of values you have to work with and it's less likely you'll ever exceed the string length limitation of a ValueList rowsource. I use the Open event of the form to assign the output from a custom function to the rowsource of the combobox.
    Charlotte

Posting Permissions

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