Listbox dilemma

[jopaumier]

I have a pair of listboxes, the first populated with category names from a database. The user will be able to select one or more category names in the first listbox and move them over to the second list box. The process can be reversed (moving names from the 2nd back to the 1st) and redone (1st to the 2nd) as many times as the user likes. Works great.

The data in the 2nd listbox will be used in a SQL query later on. My problem is the category names (chemical names to boot) can be very long, many that are 20-30 characters, and I have no say in the naming. There is a category CODE associated with each category NAME. Is there a way to easily track the codes back and forth as the user moves names between listboxes so I can use the codes in the SQL?

I've thought about arrays, but it seems risky - e.g., an array with 10 elements and removing elements 3 and 8 then 'removing' the empty elements. I could concatenate the code & name and display the concatenated results in the listboxes, but I'd rather not have the user see the category codes since they are meaningless to the user and likely to cause confusion. Thread 222-426213 (Neenas19) answers my question if if have to concatenate, but I'd rather not show those codes.

Thanks,
Jim

 

[AndyWatt]

Can you not assign the code value to the Tag property of each ListItem?

Dim itm As ListItem

set itm = lvwTarget.ListItems.Add
With itm
.Text = "Nasty Chemical Name"
.Tag = <Code>
End With

Where <Code> is the code value you want to track.


Regards,

Andy

&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux

 

[JS74]

Try out an temporary recordset. Open it with no connection and add the fields you need. Then you can insert as much values as you want and save them to the database later.

I hope I could help
Joe

 

[bjd4jc]

If you are using a straight list box and not a ListView control (as AndyWatt provided code for) then you can use the ItemData property instead of the Tag property. Here is an example.

ListBox1.Add &quot;Value&quot;
ListBox1.ItemData(ListBox1.NewIndex) = Code 'Must be numeric

This is probably the easiest way to do this if you're using a straight List Box and the Code is numeric. Otherwise, if you're using a straight list box and code is not numeric use JS74's method. My opinion anyway.

 

[jopaumier]

Thank you to all for responding - it is nice to post a message at the end of the day and have three responses the next morning. Time to try them out.

Jim