How to populate a list box using adodb recordset

The error occurs when I assign the rs to the list box.

Thanks,

Steve C.

Private Sub sortData()
On Error GoTo Err_catch

dim dbConn as New ADODB.Connection

dim rs as New ADODB.Recordset

dim strSQL as String

Set dbConn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Create database connection
dbConn.Open "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & CurrentProject.Path & "\data\NED_DATA.mdb;"

strSQL = "SELECT [ID],[GLOBAL STATUS], [AFFILIATION],[REGION]," & _
" [COUNTY], [RN], [A DATE]," & _
" [A STATUS], [GLOBAL], [ENTITY NAME]," & _
" [TYPE], [LASTNAME], [FIRSTNAME]," & _
" [PHONE], [ADD 1], [ADD 2]," & _
" [CITY], Storm, [ZIP], [PROJECT]" & _
" FROM qryAssignedWork" & _
" WHERE [PROJECT] = '" & Trim(strProjectName) & "'" & _
" ORDER BY [" & cbo1Sort & "],[" & cbo2Sort & _
"],[" & cbo3Sort & "],[" & cbo4Sort & "]"

With rs
.Source = strSQL
.ActiveConnection = dbConn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

cboFind = ""
txtFind = ""

' The error occurs here
lstAssignedWork.Recordset = rs
lstAssignedWork.ColumnHeads = True
lstAssignedWork.ColumnCount = 19
lstAssignedWork.ColumnWidths = "600;" & _
"1300;" & _
"800;" & _
"950;" & _
"950;" & _
"950;" & _
"1000;" & _
"1200;" & _
"700;" & _
"1900;" & _
"1900;" & _
"950;" & _
"950;" & _
"1200;" & _
"1700;" & _
"950;" & _
"950;" & _
"400"
lstAssignedWork.Requery

Exit_catch:
Exit Sub
Err_catch:
MsgBox Err.Description
Resume Exit_catch
End Sub



Answer this question

How to populate a list box using adodb recordset

  • R.Tutus

    Same problem here,

    I am trying to populate a datalistbost with records from an Access table. What I am trying to achieve is: The user presses a command button and a connection is made to the Access Table and fields are populated in the listbox.

    It's possible to do this in the visual interface with point and click operations but demand on the computer resources will be overwhelming.

    So we are awaiting any help out there.

    Private Sub cmdRetrieve_Click()
    Dim EditConn As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim rs1 As New Recordset
    Dim strConnection As String

    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source = J:\Database Project\SYSTEMS.MDB;"


    EditConn.ConnectionString = strConnection
    EditConn.Open

    cmd1.CommandText = "Departments"
    cmd1.CommandType = adCmdTable
    Set rs1 = cmd1.Execute


    End Sub


  • atirado

    Hi, i am having a similiar problem. In your case, should you not use the SET command to bind your listbox to the recordset E.g.

    Set Me.lstAssignedWork.Recordset = rs

    Hopefully more people will respond.

    .


  • How to populate a list box using adodb recordset