PickList Custom Class (1 Viewer)

moke123

AWF VIP
Local time
Yesterday, 21:47
Joined
Jan 11, 2013
Messages
3,852
Cross Posted here ... https://www.accessforums.net/showthread.php?t=83096&p=472473#post472473

As noted in my cross post, I was toying around with a custom class for a picklist utilizing a dictionary object. I want to use a dictionary due to the fact that it is much easier to add and remove elements to it than other types of arrays or collections .(IMO anyway)

To use it only requires 2 lines of code in your form. One to instantiate the class and one to initiate the instance.
You only need to pass the 2 listbox objects to the class. Obviously you need a reference to Microsoft Scripting Runtime for the dictionaries.

In your selections listbox or left side listbox, set your column count and widths as well as your where clause and order by. The only requirement is
that the rowsource query is based on a table with the primary key in the first column.

The selected listbox or right side listbox does not need anything. Just drop it on your form. The settings from the left side listbox are automatically
transferred over.

I'm looking for any suggestions to improve it or to find out how you broke it.
 

Attachments

  • ccPickList_v2.zip
    58.2 KB · Views: 350

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:47
Joined
May 21, 2018
Messages
8,463
@moke123
I have a similar capability I use, I call it a "From To List Control". My instantiation is very different, but does much of the same. Here are some ideas I added to give it more utility..

1) I made this a subform as well. Then the user drops this onto a form as if it is a single control. Makes it easy to use throughout the db. The only issue with this is that you cannot hard code the Rowsource of the listbox. You have to instantiate it and pass in the row source as a property of the subform. This way you can have many forms using this "control"

2) Often you want to know and react to an item being added or removed. I added a custom events, so you can react immediately if a value is added or subtracted to the list. Lets say you want to add a value to a junction table if you add it to the right list or remove if subtracted from the right list. And you want to show your updates immediately. If not you are stuck waiting until you are done. I think I added events that passed back the primary key of the value added or removed that can be trapped.
ValueToAdded (ValueID)
ValueToRemoved(ValueID)
I may have added the same for ValueFrom, but I doubt that get used much.
This way the main form can trap when an item moves and do something.

4) I added properties so you can pull all selected values and all unselected values. This property is a collection of PKs. Could be an array.
In the main form then you can have something like

dim collSelected as collection
set collSelected = clsPL.SelectedItems
for I = 1 to CollSelected.Count
debug.print CollSelectedI(i)
next i

5) I added other properties to get a filter string. this is a string

dim ItemsIN as string
itemsIN = clsPL.ItemsINselection

this would return something like
(1,55,88, 130)
 

moke123

AWF VIP
Local time
Yesterday, 21:47
Joined
Jan 11, 2013
Messages
3,852
Thanks @MajP.
This $hit is all your fault going back to at least this post https://www.access-programmers.co.uk/forums/threads/im-back-with-a-challenge-custom-controls.309240/ :) I'm still struggling with your graduate level classes.

I'm gonna add 4 & 5 as those are an easy addition. I hadn't given much thought to what to extract from the process other than the one command button that iterates through the listbox. Just as easy to have a public function in the class to do things like that.

Thanks.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:47
Joined
May 21, 2018
Messages
8,463
Here is the version I did, and demonstrates using some of those ideas. I was trying to think of different ways you would want to use it. I often get people suggesting to make this a multi select. I do not see the utility if I can double click to move an item. I also added the ability to choose to fire on single click. In my mind that is better than multiselect. Multi select for me causes a lot of issues. This also demonstrates one thing that confuses people. Form classes are just "classes" and can be treated the same as a custom class. So you can have public property and methods.
 

Attachments

  • FromToList Generic SingleSelect v3.accdb
    1.3 MB · Views: 374

moke123

AWF VIP
Local time
Yesterday, 21:47
Joined
Jan 11, 2013
Messages
3,852
I rarely use picklists as I always felt they required to much work (coding and queries, etc)
I've always used multi-select listboxes with a public function which always gives me what I need.

here's the function with examples
 

Attachments

  • MSListBX.accdb
    508 KB · Views: 305

moke123

AWF VIP
Local time
Yesterday, 21:47
Joined
Jan 11, 2013
Messages
3,852
I added a function to return the selected values in various formats, along with a way to test out the arguments.

DemoLB.jpg
 

Attachments

  • ccPickList_v3.accdb
    736 KB · Views: 389

Users who are viewing this thread

Top Bottom