Help needed with moving items between two listboxes

[JodiPhillips]

Hello everyone, there are many questions and answers relating to moving items between two listboxes here and on the net in general, however, none answer my specific problem.

I have two listboxes on a form. The first listbox is populated according to command buttons (Command 14 & Command 15) that are clicked by the user (draws data via SQL statement - see code below). The second listbox is populated by user selection from the first listbox. I can get this to work to a point. It incorrectly replaces any previous selection moved to List2 whenever a new selection is made in List1. That is List2 will not store anything more than the last selection made. There is also "add" and "remove" command buttons on the form (on click event moves the items either way - though code for remove is not done as yet until "add" works correctly!). Once List2 is populated to the users satisfaction the data will be added to a table. This in effect is a shopping list for trainee's to select from to add to their training needs, and due to the substantial number of topics available in our organisation, the topics are categorised into a curriculum. The command buttons "Command14" and "Command15" display in List1 only those topics for particular curricula. (There will be more command buttons added once I can get this to work). Hope this makes sense :)

(BTW - Control names will be tidied up after code executes correctly.)

Properties for List1 (lstAvail)
Multi select "Extended"; BoundColumn 1; ColumnCount 1; RowSourceType = Table/Query; RowSource = "".
Properties for List2 (lstSelected)
Multi select "Extended"; BoundColumn 1; ColumnCount 1; RowSourceType = Value List; RowSource = "".

Can anyone give me a hint as to why my code wont store each and every selection passed to List2 from List1? It appears that my "add" button just overwrites data held in strItems variable instead of adding to it. Any help is appreciated.

Code

Option Compare Database
Option Explicit
 
Dim dbs As DAO.Database
 
Private Sub Command14_Click()
 
 
 
Dim strSQL As String
Dim Ctype As String
 
 
On Error GoTo Err_Command14_Click
 
Set dbs = CurrentDb
 
 
Ctype = "NTO"
 
strSQL = "SELECT TopicName FROM ShoppingList" _
 & " WHERE CurricType = '" _
 & Ctype & "'"
 
Debug.Print strSQL
   Me.lstAvail.RowSourceType = "Table/Query"
   Me.lstAvail.RowSource = strSQL
 
Exit_Command14_Click:
    Exit Sub
 
Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
 
End Sub
Private Sub Command15_Click()
Dim strSQL As String
Dim Ctype As String
 
 
On Error GoTo Err_Command15_Click
 
Set dbs = CurrentDb
 
 
Ctype = "NTC"
 
strSQL = "SELECT TopicName FROM ShoppingList" _
 & " WHERE CurricType = '" _
 & Ctype & "'"
 
Debug.Print strSQL
   Me.lstAvail.RowSourceType = "Table/Query"
   Me.lstAvail.RowSource = strSQL
 
Exit_Command15_Click:
    Exit Sub
 
Err_Command15_Click:
    MsgBox Err.Description
    Resume Exit_Command15_Click
 
End Sub
 
Private Sub Command22_Click()
    CopySelected Me
End Sub
 
Public Sub CopySelected(ByRef frm As Form)
 
    Dim ctlSource As Control
    Dim ctlDest As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
 
 
    Set ctlSource = frm!lstAvail
    Set ctlDest = frm!lstSelected
 
 
        For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            strItems = strItems & ctlSource.Column(0, _
                 intCurrentRow) & ";"
        End If
   Next intCurrentRow
 
        Debug.Print strItems
 
    ctlDest.RowSource = ""
    ctlDest.RowSource = strItems
 
 
    Set ctlSource = Nothing
    Set ctlDest = Nothing
 
 
End Sub

[nico5038]

I've coded such a selection by using one table with an additional YesNo field.
The first combo box selects the "False" rows, the second the "True" rows.
Now the Move is just an update of the YesNo field. Your button 14/15 can be changed into a combo box with two (or three when "All" is needed too) to do a filtering on the first list box. In the afterupdate event the filter for the query can be set.
When you change the list box into a datasheet subform you could even link the combo selection box so the rows are automatically filtered and/or the user can use the right-click pop-up to do his (her) own filtering.

I've attached a sample showing this solution in a list box and in a sub form data sheet form to a comment in http://bytes.com/topic/access/answer...tem-copy-paste.

Nic;o)

[JodiPhillips]

Nico! :)

I'd actually read that thread and downloaded the sample.mdb previously in my searches through this site (I always search on your posts first!! hehe). I couldn't get the sample to work as it kept throwing up a 3038 runtime error. After your post here I thought I would take another look at the sample and it finally occurred to me that the 3038 error was because the file came down in read-only state. Voila! Fixed, I can now see what your sample does. It looks like exactly what I need to do. I will work on it now and let you know how I go :)

Thanks again Nic <hugs>

Jodi

[nico5038]

Always glad when my samples are useful :-)

I wold advise to use the datasheet solution and use a "linked" combobox for the left datasheet. Just put "NTC", "NTO" and "*" in there as valuelist and use a filter with "LIKE" for that field in the subform query. Thus the "*" will select all.

Additional benefit will be that selected entries won't show when they are in the right datasheet.

Keep me posted and let me know when stuck.

Nic;o)