Add items from query to listbox with vba

[Montez659]

I know this sounds simple enough, but there is a little twist. Usually, I would just set the row source to the query and we are all good to go. However, in this case, I want the user to be able to double-click to remove the selected item from the listbox. It will eventually get transferred to a different listbox.

In order to do this, the listbox row source type has to be value list, which doesn't allow me to put in an SQL statement to grab the query info.

I need some direction on how to go about doing this. Thanks in advance for the help!

 

[SydneyGeek]

This is from bigresource. I formatted it because the code was messy and came out as a single line.

Article...
http://www.bigresource.com/VB-Easily-Move-Items-From-One-Listbox-To-Another-fCP4y4Xt7M.html

Code...

Private Sub MoveLBItems(FromListBox As ListBox, ToListBox As ListBox, Optional MoveAll As Boolean)
    Dim i As Long
    With FromListBox
        If MoveAll = True Then
            For i = 0 To .ListCount - 1
                ToListBox.AddItem .List(i) 'move all items to Tobox
            Next
            .Clear
        Else
            For i = .ListCount - 1 To 0 Step -1
                If .Selected(i) = True Then ' if selected then
                    ToListBox.AddItem .List(i) ' add to other list
                    .RemoveItem i ' remove
                End If
            Next
        End If
    End With
End Sub
Private Sub cmdMoveAll_Click()
    MoveLBItems List1, List2, True
End Sub
Private Sub cmdMoveSelected_Click()
    MoveLBItems List1, List2
End Sub
Private Sub cmdMoveSelectedBack_Click()
    MoveLBItems List2, List1
End Sub
Private Sub cmdMoveAllBack_Click()
    MoveLBItems List2, List1, True
End Sub

Denis

 

[Montez659]

Denis,

Just got back to this post and I appreciate the info. What I am needing in addition to this, though, is how to populate the listbox initially through vba as it will be a value list and I can't just set the rowsource property to the query.

That is a nice simple snippet though! That's going in my diigo!

 

[Montez659]

Actually, nix this, unless you just know the info and can share it. I changed up my structure a bit so that I just do UPDATE and INSERT INTO's therefore updating the table and requerying the listbox after. I think this is much easier than what I was trying to do.

 

[Norie]

Montez

You can add/remove items to a listbox without using a query or recordsource.

You just use the methods that are in the code Denis posted, eg AddItem, RemoveItem...

In fact as far as I know you have to set the source property to Value (List?) to be able to us them.

 

[Montez659]

Thanks. I guess what I was really trying to get at was how to, in VBA, loop through the query records in order to use the AddItem method. If the rowsource is set to value list, you can't use a standard query/table or SQL statement, you have to physically add them.

The adding part I can do, it is that I am just a bit dusty on pulling the info from the query to add them via the additem method.

 

[SydneyGeek]

This should work...

Build a Recordset based on the query.
Then loop through the desired field, using AddItem to populate the list / combo.

Denis