Delete SQL query in VBA not working

[Triscuit]

'Delete command button removes selected laboratory number and calibration group from the tblCalibrationGroup
'Currently broken

Private Sub Command12_Click()

    For i = 0 To LstSearch.ListCount - 1
        If LstSearch.Selected(i) Then
            Dim strSQL As String
            strSQL = " DELETE * FROM tblCalibrationGroup " & _
            "WHERE [txtLabNum] = ' " & Me.LstSearch.Column(0, i) & " ' " & _
            "AND [txtCalibGroup] = ' " & Me.cboFilter & " ' "
            DoCmd.RunSQL (strSQL)
        End If
    Next i

End Sub

I have a Table Name tblCalibrationGroup and when in a certain form I click a "Delete" command button that runs the code above to get rid of a entry in the table. The query should pull data from the form, it goes through the entire listbox (Me.LstSearch) and deletes all entries in the table where the txtLabNum matches items selected in the list box and the txtCalibGroup matches the value in a combobox (Me.cboFilter).

When nothing it selected, nothing happens as controlled by the If statement.

However when something is selected it gives the warning that it's deleting "0" rows from the specified table, WHEN it should really be deleting at least one or more selected rows from the table.

What's the deal, any suggestions?

 

[pbaldy]

Are both fields text? Presuming so, I'd get rid of the spaces between the single quotes and the value. You want the end result to be:

'Paul'

not

' Paul '

Also, I'd loop the selected items only, rather than all items. It would be more efficient. Here's an example:

http://www.baldyweb.com/multiselect.htm

 

[Triscuit]

Thank you PBaldy.

Deleting all of the spaces between the text quotes gave a syntax error but with some guess and check the form

'" & Me.LstSearch.Column(0, i) & "'

works.

Gonna give your multiselect loop a whirl next.

 

[pbaldy]

Glad it worked. I only mentioned taking out the spaces between the single quotes and the values. The others were optional except the one at the very end of the WHERE line, as it provided the spacing between words.