MultiSelect Listbox to edit records

[Chalkie42]

Hello again folks. I have been knocking this about for a full day and can work it out so any help would be much appreciated.

I have a table with client records (tblClientList). I have a form which presents a list of all available clients in a multiselect listbox. I want to update each of the records selected but my code only edits the first record in the list box regardless of whether it has been selected. I know it's something simple but I can't see the wood for the trees.

My code looks like this:

Private Sub Command59_Click()
On Error GoTo Command59_Click_Err

Dim DB As Database
Dim rs As DAO.Recordset
Dim varItem As Variant


Set DB = CurrentDb()
Set rs = DB.OpenRecordset("tblClientList")

    
    If IsNull(Me.RemovedReason) Or Me.RemovedReason = "" Then
        MsgBox ("You have not selected the reason for removal")
        Me.RemovedReason.SetFocus
        Exit Sub
    End If
    
    If lstData.ItemsSelected.Count = 0 Then
    MsgBox "There are no records selected", vbInformation, "No Records Selected."
    Else
    
    With rs
    For Each varItem In lstData.ItemsSelected
            .Edit
            .Fields("RemovedDate") = Me.DateRemoved
            .Fields("Removed") = Me.Removed
            .Fields("RemovedBy") = Me.RemovedBy
            .Fields("RemovedReason") = Me.RemovedReason
            .Update
       
        Next varItem
    End With
    End If
    Me.lstData.Requery
        
    rs.Close
    Set rs = Nothing
    Set DB = Nothing

    
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
    'DoCmd.Close acForm, "frmArchiveClient"


Command59_Click_Exit:
    Exit Sub

Command59_Click_Err:
    MsgBox Error$
    Resume Command59_Click_Exit
    
End Sub

Many thanks.

 

[sneuberg]

The recordset rs doesn't move anywhere in your code. There nothing like a rs.MoveNext or some sort of lookup. So rs just sits there pointing to the first record in tblClientList.

You might consider a difference approach and that would be to iterate through the listbox generating a list of the keys of the records that need to be updated. Then with that list you could update the table with a single statement that would have this list in a WHERE IN clause of the update statement. While not specifically for an update statement here are two link with code that develops the list from the listbox

http://allenbrowne.com/ser-50.html
https://support.microsoft.com/en-us/kb/135546