Remove selected items from multiselect listbox

[Yusuf]

Hi

Is there a quick way of doing this.
I have quite a few listboxes that operate on one screen. The user can select items from any of the listboxes and a Refresh command button will trigger the items to be removed.

If it's possible, are you able to advise me on a method or code for only one listbox.
I would simply mirror this code for the other listboxes

Many thanks
Y

 

[SydneyGeek]

Untested, but see how this goes...
Adjust table, field and listbox names to suit.

Note: This assumes that the bound column is a numeric ID field.

    Dim i As Integer
    
    DoCmd.SetWarnings False
    For i = lstSelected.ListCount - 1 To 0 Step -1
        If lstSelected.Selected(i) Then
            DoCmd.RunSQL "DELETE * FROM [SomeTable] WHERE [SometableID]=" & lstSelected.Selected(i)
            lstSelected.RemoveItem i
        End If
    Next i
    DoCmd.SetWarnings True

Denis

 

[Trevor G]

Trevor

But there isn't in early versions.

The error message the OP describes indicates they are using an earlier version than 2007.

I can't quite see the use of either method in Access, normally when working with a listbox it would be bound to a record source.

I do agree Norie that it isn't available in earlier versions. I sometimes allow users to select field names from a list box to create adhoc queries and if they add to many in Access 2007 then they can remove the field name.

I hope I am not out of line, just trying to help.

 

[Norie]

Trevor

Not at all.

If you are talking about a listbox with field names it's most unlikely an unbound listbox.

There shouldn't be any problem using Add/RemoveItem if that's the case.

It's when the listbox is bound to a recordsource like a table or query that there might be issues.

In Excel I've seen example where the listbox has been populated from a range and when you try and use these methods VBA doesn't like it.

 

[Yusuf]

Hi Denis

Thank you for your efforts.

Unfortunately I still get the Compile error: Method or Data member not found

When I comment out the line : 'lstSelected.RemoveItem i
then I get this
Runtime error: Data type missmatch in criteria expression

Any suggestions?

Regards
Y

 

[Norie]

Yusuf

Try requerying the listbox or form to update the recordsource.

 

[Yusuf]

Hi Norie

Would I requery before or after the next i bit? (I've not done this before)
Also, does this eliminate the Compile error?

Regards
Y

 

[Norie]

Yusuf

Just remove that line of code - whatever version of Access you are using does not appear to support RemoveItem.

You could requery whenever you want, but I would think the most sensible time to do that would be after the loop.

Otherwise you are going to be requerying every time you remove an item.

 

[Yusuf]

Hi

By the example Denis sent earlier, I have modified it to this and I'm still getting this error;
Runtime error: Data type missmatch in criteria expression

Dim i As Integer

DoCmd.SetWarnings False
For i = List2.ListCount - 1 To 0 Step -1
If List2.Selected(i) Then
DoCmd.RunSQL "DELETE * FROM [table1] WHERE [field1]=" & List2.Selected(i)
' List2.RemoveItem i
End If
Next i

List2.Requery
DoCmd.SetWarnings True

End Sub

My apologies for dragging this on. I'm really stuck with this one and cannot move forward if it's not sorted.

Regards
Y

 

[SydneyGeek]

Hi Denis

Thank you for your efforts.

Unfortunately I still get the Compile error: Method or Data member not found

When I comment out the line : 'lstSelected.RemoveItem i
then I get this
Runtime error: Data type missmatch in criteria expression

Any suggestions?

Regards
Y

If the bound column in the listbox is a text field (not a numeric ID) then you will need to change the code -- see below

This line:

            DoCmd.RunSQL "DELETE * FROM [SomeTable] WHERE [SometableID]=" & lstSelected.Selected(i)

Should be changed to this:

            DoCmd.RunSQL "DELETE * FROM [SomeTable] WHERE [SometableID]='" & lstSelected.Selected(i) & "'"

</pre>
Denis

 

[Yusuf]

Denis
I have this feeling that I'll be kicking myself as this is probably something so simple that I'm just not getting!

Please indicate where I'm going wrong.
Field1 is Text and when items are selected, I do not get an error but nothing happens and both the table and the listbox contain selected items

Dim i As Integer

DoCmd.SetWarnings False
For i = List2.ListCount - 1 To 0 Step -1
If List2.Selected(i) Then
DoCmd.RunSQL "DELETE * FROM [table2] WHERE [field1]='" & List2.Selected(i) & "'"
' List2.RemoveItem i
End If
Next i

List2.Requery
DoCmd.SetWarnings True

End Sub

Regards
Y