By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Help needed with moving items between two listboxes

JodiPhillips
P: 26
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim dbs As DAO.Database
  5.  
  6. Private Sub Command14_Click()
  7.  
  8.  
  9.  
  10. Dim strSQL As String
  11. Dim Ctype As String
  12.  
  13.  
  14. On Error GoTo Err_Command14_Click
  15.  
  16. Set dbs = CurrentDb
  17.  
  18.  
  19. Ctype = "NTO"
  20.  
  21. strSQL = "SELECT TopicName FROM ShoppingList" _
  22.  & " WHERE CurricType = '" _
  23.  & Ctype & "'"
  24.  
  25. Debug.Print strSQL
  26.    Me.lstAvail.RowSourceType = "Table/Query"
  27.    Me.lstAvail.RowSource = strSQL
  28.  
  29. Exit_Command14_Click:
  30.     Exit Sub
  31.  
  32. Err_Command14_Click:
  33.     MsgBox Err.Description
  34.     Resume Exit_Command14_Click
  35.  
  36. End Sub
  37. Private Sub Command15_Click()
  38. Dim strSQL As String
  39. Dim Ctype As String
  40.  
  41.  
  42. On Error GoTo Err_Command15_Click
  43.  
  44. Set dbs = CurrentDb
  45.  
  46.  
  47. Ctype = "NTC"
  48.  
  49. strSQL = "SELECT TopicName FROM ShoppingList" _
  50.  & " WHERE CurricType = '" _
  51.  & Ctype & "'"
  52.  
  53. Debug.Print strSQL
  54.    Me.lstAvail.RowSourceType = "Table/Query"
  55.    Me.lstAvail.RowSource = strSQL
  56.  
  57. Exit_Command15_Click:
  58.     Exit Sub
  59.  
  60. Err_Command15_Click:
  61.     MsgBox Err.Description
  62.     Resume Exit_Command15_Click
  63.  
  64. End Sub
  65.  
  66. Private Sub Command22_Click()
  67.     CopySelected Me
  68. End Sub
  69.  
  70. Public Sub CopySelected(ByRef frm As Form)
  71.  
  72.     Dim ctlSource As Control
  73.     Dim ctlDest As Control
  74.     Dim strItems As String
  75.     Dim intCurrentRow As Integer
  76.  
  77.  
  78.     Set ctlSource = frm!lstAvail
  79.     Set ctlDest = frm!lstSelected
  80.  
  81.  
  82.         For intCurrentRow = 0 To ctlSource.ListCount - 1
  83.         If ctlSource.Selected(intCurrentRow) Then
  84.             strItems = strItems & ctlSource.Column(0, _
  85.                  intCurrentRow) & ";"
  86.         End If
  87.    Next intCurrentRow
  88.  
  89.         Debug.Print strItems
  90.  
  91.     ctlDest.RowSource = ""
  92.     ctlDest.RowSource = strItems
  93.  
  94.  
  95.     Set ctlSource = Nothing
  96.     Set ctlDest = Nothing
  97.  
  98.  
  99. End Sub
Dec 11 '08 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 11 '08 #2

JodiPhillips
P: 26
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
Dec 11 '08 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.