Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003

    Unhappy Unanswered: Listbox of reports not displaying anything


    I am trying to create a function which will display all of the reports in my database via a listbox - I am using the following function to grab the reports - and then I use the results has the row source type. when the form runs the listbox is not populated - can anyone tell me why this occurs?!
    The function is pasted in below,

    Any suggestions would be great!


    Function ReportsDisplay(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Dim db2 As Database, dox As Documents, I As Integer
    Static RptName(255) As String
    Static iRptCt As Integer
    Select Case code
    Case acLBInitialize
    Set db2 = CurrentDb()
    Set dox = db.Containers!Reports.Documents
    iRptCt = dox.Count
    For I = 0 To iRptCt - 1
    RptName(I) = dox(I).Name
    ReportsDisplay= True
    Case acLBOpen
    ReportsDisplay= Timer
    Case acLBGetRowCount
    ReportsDisplay = iRptCt
    Case acLBGetColumnCount
    ReportsDisplay = 1
    Case acLBGetColumnWidth
    ReportsDisplay = 2 * 1440
    Case acLBGetValue
    ReportsDisplay = RptName(row)
    Case acLBEnd
    Erase RptName
    iRptCt = 0
    End Select
    End Function

  2. #2
    Join Date
    Aug 2002
    Listboxes will only accept arrays...

    Here is some sample code that I use to populate listboxes from modMain:

    Public Function EnumAnything(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    'Purpose: Supplies the 'anythings' to a listbox by opening a recordset,
    ' converting it into an array, which is the only thing the listbox will accept
    'Usage: Set the listbox's RowSourceType property to EnumAnything and leave
    ' its RowSource property blank
    'Notes: All arguments are provided to the function automatically
    'Author: Maia 05/20/02

    Dim db As Database
    Dim rst As Recordset
    Static iCount As Integer
    Static aAnything As Variant

    'respond to the supplied value of code

    Select Case code

    Case acLBInitialize 'called once when form opens, initializes the listbox
    Set db = CurrentDb()

    Set rst = db.OpenRecordset("SELECT...." statement goes here)

    If rst.RecordCount = 0 Then
    MsgBox "No anythings have been found.", vbOKOnly
    aAnything = ""
    'get number of rows
    iCount = rst.RecordCount

    'populate array with recordset

    With rst
    aAnything = .GetRows(.RecordCount)
    End With
    EnumAnything = iCount

    End If

    Set db = Nothing
    Set rst = Nothing

    Case acLBOpen
    EnumAnything = Timer

    Case acLBGetRowCount
    EnumAnthing = iCount

    Case acLBGetColumnCount
    EnumAnthing = 4 'set your column count here

    Case acLBGetColumnWidth
    EnumAnything = True

    Case acLBGetValue
    EnumAnything = aAnything(col, row)

    Case acLBEnd
    Erase aAnything
    iCount = 0

    End Select

    End Function

  3. #3
    Join Date
    Jan 2003
    Thanks for that,

    Sorry to bother you again but how would I create a sql statement for the recordset which would select the reports in the database window?!

  4. #4
    Join Date
    Aug 2002
    The easiest way to do that is to go into design mode for a query and build what you want there. Then switch to SQL View from the menu, and copy and paste that code. You may have to fiddle a bit with quotes and parens, but it works well. You're welcome and good luck.

  5. #5
    Join Date
    Jan 2003
    Aberdeen, Scotland, UK
    I think the other persons idea was a bit overcomplicated. Here's a simple bit of code that'll fill a listbox with the names of all reports in a database.

    Dim Ctr As Container
    Dim Db As Database
    Dim Doc As Document
    Dim StrName As String
    Dim StrReportList As String

    Set Db = CurrentDb()
    Set Ctr = Db.Containers("Reports")


    For Each Doc In Ctr.Documents
    StrName = Doc.Name
    StrReportList = StrReportList & StrName & ";"
    Next Doc

    Me.LstReports.RowSourceType = "value list"
    Me.LstReports.RowSource = StrReportList

  6. #6
    Join Date
    Jan 2003
    Thanks a million,

    I tried that out and it is working fine.



Posting Permissions

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