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
Expand|Select|Wrap|Line Numbers
- 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