docmd.runsql clears my listbox selections

[r15gsy]

I have a form with a multi-select listbox of id's and names and addresses.

I have vba code which loops through the multi-select listbox to make a criteria string of id's.

I use the string to create some SQL which updates the names or addresses of all the id's selected.

My problem is that the listbox refreshes and clears my selection.

I want to keep the selection so that it can be re-used.

Any suggestions?

 

[Remou]

Please post your code.

 

[r15gsy]

Hi

I'm not sure my code will help, but here it is.


The listbox is bound to the table being updated, which is what I think is causing the problem. In my code the listbox selection is lost when the line 'DoCmd.RunSQL strSQL' is run, and the code afterwards has no effect.

If I remove the docmd.runsql strsql then the selection remains and the remaining code works.

Thanks.

Private Sub cmdUpdate_Click()
On Error GoTo Err_Handler
    
    Dim varItem As Variant      'Selected items
    Dim strCriteria As String, strSQL As String
    Dim lngLen As Integer
    Dim response
    
    strCriteria = ""
    strSQL = ""
    
    'If no items selected, show a message that none selected
    If Me.List9.ItemsSelected.Count = 0 Then
        MsgBox "You have not selected any images", vbExclamation, "Error"
    Else
        'Ask user to confirm they want to update selected records
        response = MsgBox("You have selected " & Me.List9.ItemsSelected.Count & " records to update." & vbNewLine & vbNewLine & "Are you sure you want to continue?" & vbNewLine & vbNewLine & "Note: this operation cannot be undone.", vbYesNo, "Update?")
        If response = vbYes Then
            'Loop through the ItemsSelected in the list box.
            With Me.List9
                For Each varItem In .ItemsSelected
                    If Not IsNull(varItem) Then
                        'Build up the filter from the bound column (hidden).
                        strCriteria = strCriteria & "'" & .ItemData(varItem) & "', " '& .Column(1, varItem)
                    End If
                Next
            End With
            
            strCriteria = Left(strCriteria, Len(strCriteria) - 2)
            
            strSQL = "UPDATE tblImage SET tblImage.strImageName = '" & strUpdateText & "' WHERE (((tblImage.strImageRef) In (" & strCriteria & ")));"
(" & strCriteria & ")));"
            
            Debug.Print strSQL
            
            DoCmd.RunSQL strSQL
            
            response = MsgBox("Do you want to keep the items selected in the list?", vbYesNo, "Keep Selected?")
            
            If response = vbYes Then
                'Do nothing
            Else
                Call ClearList(Forms!frmimagedisplay!List9)
            End If
        
        Else
            'Do nothing
        End If
        
    End If
    
    
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

 

[r15gsy]

I have created a new form with just this code and the problem does not recur, so must be other code in my form. I will work through it.