load selected data into the listbox

I have two list boxes lstSelected and lstAll. lstSelected is empty and lstAll if filled from the database.
Also there are two buttons "add" and "remove".
"add" button move selected items from lstAll to lstSelect, and "remove" removes selected items from lstSelect.

I would really appreciate some vba code
LVL 35
YZlatAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
In your table include a check box field (Yes/No).  Call this SelectedYN (defaut is false).

lstAll , row source:
Select Name From MyTable Where SelectedYN = 0     '<--- lstAll will show everything in the beginning

With following code in its DobleClick event of lstAll:

CurrentDB.Execute "Update MyTable Set SelectedYN =-1 Where Name ='" & me!lstAll & "'"
RefrshLisBoxes

'-----------
lstSelected, row source:
Select Name From MyTable Where SelectedYN = -1     '<--- lstSelected will show nothing in the beginning

With following code in its DobleClick event of lstSelected:

CurrentDB.Execute "Update MyTable Set SelectedYN =0 Where Name ='" & me!lstSelected & "'"
RefrshLisBoxes

'----------
finally, paste this code in code window of your form:

Sub RefrshLisBoxes()
lstAll .RowSource=lstAll .RowSource
lstSelected.RowSource=lstSelected.RowSource
End Sub

Mike
0
 
donpricejrCommented:
Go to the following URL: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q233/3/24.asp&NoWebContent=1

There you will find an example, which uses drag and drop to populate multi-select combo boxes. You may adapt this to your needs.

-Don
0
 
joekendallCommented:
Mike is right on it. That is what I have done in the past to implement your need.

Joe
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
YZlatAuthor Commented:
no, that's not what I need. I have two list boxes. one- lstAll I filled with fields from the database, the other one is empty. In between the list boxes I have two buttons "add" and "remove". What I want is when a user selects one or more items from the lstAll list box and clicks on "add", I want those items to be copied into the second list box lstSelected. "remove" button should delete the selected items from lstSelected
0
 
joekendallCommented:
What Mike posted will do that.

Do you want them to appear in the lstAll if they are now in lstSelected?

Joe
0
 
YZlatAuthor Commented:
I don't want to add another database field. it has nothing to do with database. All I want is to copy selected items from one list box into another
0
 
joekendallCommented:
Dim v As Variant
   
For Each v In lstAll.ItemsSelected
    lstSelected.RowSource = lstSelected.RowSource & lstAll.Selected(v) & ";"
Next

lstSelected.RowSource = Left$(lstSelected.RowSource,Len(lstSelected.RowSource)-1)

Joe
0
 
YZlatAuthor Commented:
Here is my code. It seems right but items don't get copied into lstSelected:

qrySQL = "SELECT Person.PerLastName + ',' + Person.PerFirstName AS FullName " & _
        "FROM Person WHERE Person.PersonID IN("
   
    'check if at least one item is selected
    If Me("lstAll").ItemsSelected.Count > 0 Then
        For Each lstItem In Me.lstAll.ItemsSelected
           
            ID = Me.lstAll.ItemData(lstItem)
         
          If Len(strTemp) = 0 Then
                strTemp = ID
            Else
                strTemp = strTemp & ", " & ID
            End If
           
        Next lstItem
         qrySQL = qrySQL & strTemp & ");"

         Me.lstAuthor.RowSource = qrySQL
       
    Else
        MsgBox "No items were selected!", vbOKOnly
        Exit Sub
    End If
0
 
joekendallCommented:
We understand that you don't want to add another field to your table, but it is an easier way to do it since it can remain consistant and be used again for retrieval purposes.

Joe
0
 
YZlatAuthor Commented:
Joe, what's wrong with my code, why don't I get anything in lstSelected?

 'check if at least one item is selected
 
    If Me("lstAllPeople").ItemsSelected.Count > 0 Then
        For Each lstItem In Me.lstAllPeople.ItemsSelected
            ID = Me.lstAllPeople.ItemData(lstItem)
         
            qrySQL = "SELECT Person.PerLastName + ',' + Person.PerFirstName AS FullName " & _
                "FROM Person WHERE Person.PersonID = " & ID
            Set rst = db.OpenRecordset(qrySQL)
            If Not (rst.EOF And rst.BOF) Then
                AuthorName = rst(0)
            End If
            lstSelected.RowSource = lstSelected.RowSource & AuthorName & ";"
        Next

        lstSelected.RowSource = Left$(lstSelected.RowSource, Len(lstSelected.RowSource) - 1)

Else

End if
0
 
YZlatAuthor Commented:
OK, I changed lstSelected from list box to combo box cboSelected

and changed one line of code and now it's working

I changed Me.lstSelected.RowSource = qrySQL to
cboSelected.RowSource = qrySQL
0
 
YZlatAuthor Commented:
now the only thing is how do I remove an item from a combo box?
0
 
joekendallCommented:
That is not an easy thing to do nor will it happen fast. That is why Mike suggested the additional field in the table. It makes it much easier to do.

Joe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.