Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: List box does not show data in Access 2000

    I am trying to create a list box that obtains it's data from a table. I am using a user defined function called ListServerDBs as the RowSourceType. For some reason, when I open the form, there is no data listed in the list box. I've examined the different calls that are made and it looks like the acLBGetValue function is never called. I'm completely puzzled right now...and oh yeah, this works in Access 97, BUT not in Access 2000. I've listed the code below...HELP!!! I have also listed what each call returns below.

    Function ListServerDBs(fld As Control, _
    id As Variant, _
    row As Variant, _
    col As Variant, _
    code As Variant) As Variant

    Static rs As New ADODB.Recordset
    Static cn As New ADODB.Connection
    Static iNumReturned As Integer
    Static iRecCount As Integer
    Static bItemSelected As Boolean
    Static colDBList As New Collection
    Dim sConnectString As String
    Dim ReturnVal As Variant
    Dim i As Integer

    ReturnVal = Null
    Select Case code
    Case acLBInitialize
    bItemSelected = False
    ReturnVal = -1
    iNumReturned = 0
    Case acLBOpen
    sConnectString = "driver={SQL Server};server=" & SQLSERVER & ";database=master;"
    If cn.State = 0 Then cn.Open sConnectString
    rs.Open "SELECT name from sysdatabases where left(name,8)='LCDBTemp' AND name Is Not Null ORDER BY name;", cn
    rs.MoveFirst
    i = 0
    Do While Not rs.EOF
    colDBList.Add rs!Name
    i = i + 1
    rs.MoveNext
    Loop
    rs.Close
    iRecCount = i
    ReturnVal = -1
    Case acLBGetRowCount
    ReturnVal = iRecCount
    Case acLBGetColumnCount
    ReturnVal = 1
    Case acLBGetColumnWidth
    ReturnVal = -1
    Case acLBGetValue
    ReturnVal = colDBList(row + 1)
    Case acLBGetFormat
    ReturnVal = -1
    Case acLBEnd
    'rs.Close
    'cn.Close
    End Select
    ListServerDBs = ReturnVal

    End Function

    ******************
    These are the codes that are called and what they return

    acLBInitialize -1
    acLBOpen 7
    acLBGetColumnCount 1
    acLBGetColumnWidth -1
    acLbGetRowCount 7
    Then the form opens
    acLbEnd null

    thanks,
    Bobby

  2. #2
    Join Date
    Jun 2004
    Posts
    2

    Smile Problem solved

    I have figured out the problem.

    The problem had to do with the collection object that was set in the case for acLBOpen. In Access 97, when the following code was called:

    colDBList.Add rs!Name

    the Name field for the record set was added as a String. In Access 2000, the same code would add a reference to the name field that is part of my record set. When the function is called again with a different code argument, the record set was closed, hence the collection of references was pointing to nothing. In order to fix the problem, I used CString to change the object from a reference to a Cstring object.

    colDBList.Add CStr(rs!Name)

    After doing this, the list box was populated with the names from the table.

    Bobby

Posting Permissions

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