473,287 Members | 1,827 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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
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
3 5018
nico5038
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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

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

Similar topics

3
by: softengine | last post by:
Can and how do you alter a data view to include a look up field from another data table? The data table of the dataview only has the key, the value I need is in another data table. Can and how...
1
by: Ryan Ternier | last post by:
I have two listboxes, and allow users to move items between them via the following function: function SwitchList(fbox, tbox){ var arrFbox = new Array(); var arrTbox = new Array(); var...
2
by: florin | last post by:
I have to ListBox controls (or 2 html select controls with runat=server) on a web page and some buttons to move data from one listbox to another (using javascript). The problem is that when the...
8
by: Art DeBuigny | last post by:
Hi all I have tried several methods to do as follows I have a form with two listboxes and a few buttons. The listbox on the left is databound to a table, the other is loaded based on what...
3
by: Dany P. Wu | last post by:
Hi everyone, One of my Windows forms contain two listbox controls, with Add and Remove buttons between them. The idea is to allow users to select multiple items from one ListBox, click the...
6
by: Martin Heuckeroth | last post by:
Hi, We are looking for a way to determine the x and y points of the cursor in a richtext box. We made an VB.NET application with a couple of listboxes and one of them is a richtextlistbox....
2
by: Srimadhi | last post by:
Displaying selected items at the top of the listbox Hi, I am having two listboxes - one with ids and second with the related names. When user selects an item in one listbox, the corresponding...
6
by: JOSII | last post by:
Getting a string of boolean value into and out of the registry is no problem. Here's the problem: Although you can place an object into the registry and retreive it, I need to place an ArrayList...
3
by: Brian Simmons | last post by:
Hi All, I search on codeproject and google but didn't find what I was looking for. Anybody know of a good implementation where you have 2 listboxes and you want to move items between the 2. ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.