CODEWITHSUNDEEP
vbams-accesslistbox
C Zandstra
C Zandstra

Reputation: 47

Listbox.List(i) error - Method or Data Member not Found

I'm trying to use a multi-select listbox so users can select cleaning tasks they have completed and mark them as done. While looping through the list I want to see if the item is selected and create a record if so. When I try to use the .List method to return the data from a specific row, I keep getting the method not found error.

I originally did not have the forms 2.0 library loaded so I thought that was the issue, but that did not resolve the problem. I've also compacted and repaired thinking it might just be an odd fluke, but that did not help either.

'loop through values in listbox since its a multi-select
For i = 0 To listCleaningTasks.ListCount - 1
    If listCleaningTasks.Selected(i) Then
    'add entry to cleaning log
        Set rsCleaning = CurrentDb.OpenRecordset("SELECT * FROM cleaning_log;")
        With rsCleaning
            .AddNew
            .Fields("cleaning_task_id") = Form_frmCleaning.listCleaningTasks.List(i)
            .Fields("employee_id") = Me.cmbUser
            .Fields("cleanroom_id") = Me.cmbCleanroom
            .Fields("cleaning_time") = Now()
            .Update
            .Close
        End With
    End If
Next i

enter image description here

Any ideas?

Upvotes: 0

Views: 2065

Answers (2)

Vlado
Vlado

Reputation: 878

Of course the solution of June7 is correct. If you need to store the selected items and then later recall and re-select the list box items, consider to get the selected items comma delimited using this function 

Public Function GetSelectedItems(combo As ListBox) As String
    Dim result As String, varItem As Variant        
    For Each varItem In combo.ItemsSelected
        result = result & "," & combo.ItemData(varItem)
    Next        
    GetSelectedItems = Mid(result, 2)
End Function

Store it into one column of a table and after reading it back pass it to this sub:

Public Sub CreateComboBoxSelections(combo As ListBox, selections As String)
Dim N As Integer, i As Integer
Dim selectionsArray() As String
selectionsArray = Split(selections, ",")
    For i = LBound(selectionsArray) To UBound(selectionsArray)
        With combo
            For N = .ListCount - 1 To 0 Step -1
               If .ItemData(N) = selectionsArray(i) Then
                    .Selected(N) = True
                    Exit For
               End If
            Next N
        End With
    Next i
End Sub

This will select items in your ListBox as they were before.

Upvotes: 0

June7
June7

Reputation: 21379

Use .listCleaningTasks.ItemData(r) to pull bound column value from row specified by index.
Use .listCleaningTasks.Column(c, r) to pull value specified by column and row indices.

Open and close recordset only one time, outside loop.

Really just need to loop through selected items, not the entire list.

    Dim varItem As Variant
    If Me.listCleaningTasks.ItemsSelected.Count <> 0 Then
        Set rsCleaning = CurrentDb.OpenRecordset("SELECT * FROM cleaning_log")
        With rsCleaning
        For Each varItem In Me.listCleaningTasks.ItemsSelected
            `your code to create record
            ... 
            .Fields("cleaning_task_ID") = Me.listCleaningTasks.ItemData(varItem)
            ... 
        Next
        .Close
        End With
    Else
        MsgBox "No items selected.", vbInformation
    End If

Upvotes: 2

Related Questions