Search code examples
mysqlms-accessvbaadodbms-access-2016

MS Access ListBox column property with ADODB Recordset creates error 424 Object Required

I'm building a simple form in MS Access with a listbox in which I want to display data that is stored in a MySQL Server table. I want two columns to display in the listbox.

I am able to display the first column with the additem property but the second column throws the run time error message 424 Object Required.

I have searched for hours in the web an in my books but I can't figure it out. What is the problem?

Private Sub cmdSuchenVerantwortlich_Click()

Dim rsAuswahl As New ADODB.Recordset
Dim i As Long

If pConnectDB.State = adStateClosed Then
    modConnectDB.Connect_To_DB
End If

Me.lstAuswahl.RowSourceType = "Value List"

'Clear Listbox
For i = Me.lstAuswahl.ListCount - 1 To 0 Step -1
    Me.lstAuswahl.RemoveItem i
Next i


With rsAuswahl
    .ActiveConnection = pConnectDB
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient

    .Open Source:="select MATNR, AUSNAHME from Ausnahmeliste where VERANTWORTLICH = '" & Me.cboVerantwortlich & "' "


    Do Until .EOF
        Me.lstAuswahl.AddItem .Fields("MATNR").Value
        Me.lstAuswahl.Column(1, Me.lstAuswahl.ListCount - 1) = .Fields("AUSNAHME").Value

        .MoveNext
    Loop

    .Close
End With


End Sub

The code Me.lstAuswahl.Column(1, Me.lstAuswahl.ListCount - 1) = .Fields("AUSNAHME").Value throws the error messsage.

The data type in the MySQL table of MATNR and AUSNAHME is varchar.

When I hold the mouse over .Fields("MATNR").Value I can see the excpected value and holding the mouse over .Fields("AUSNAHME").Value I can see the expected value as well. So the data is the but what's wrong ?

Thanks for anybody's help.

Solution

  • To add values using the "AddItem" method of the listbox, you need to concatenate the values, separated by a comma, so a delimited string as below:

    Make sure that the columncount of your listbox is set to 2, or however many columns you want displayed.

    Me.lstAuswahl.AddItem .Fields("MATNR").Value & "," & .Fields("AUSNAHME").Value

    If you are only adding a few records, this is probably okay, but if you are going to display a lot of rows, it maybe best to transfer the data to a local table in access, and then bound your listbox directly to the table/query local to access.