Multi-Select List Box - sort when transferring values to field/row

[JohnShir]

Hi everyone,

I’m trying to transfer the contents of a multi-select list box to a row/field with commas separating the values.

I’ve done this using the MS Access 2010 wizard and it seems to work as I wanted without code - except I cannot get the values to transfer in a specific sort order. They seem to transfer in alphabetical or numerical order by default.

frm_Tasks based on tbl_Tasks has a field called “Res”.

frm_Resources based on tbl_Resources has a list box called “ResList”.

So if the user selects “Shovel” and “Fork” from ResList I would like that to be stored in Res as “Shovel, Fork” not “Fork, Shovel”.

Any help would be greatly appreciated.

Regards,

John.

[#2]

@JohnShir 

This could be done in a two-step process.

First, collect the items into an array.

Second, sort the items in that array.

I realize that is not a simple process, and arrays may be a new thing to learn. It would work, though. If you are willing to invest the effort in acquiring new skills, check it out.

Here's how arrays work.

This example is based on Excel, but the principle is the same.

[#3]

It requires code and since you don't want it, it's no of no help.

[#4]

@George_Hepworth 

Thanks for your reply. I'm not an expert on VBA, but I have written code for several databases.

I've read numerous posts about list boxes and arrays. I've seen a number of code blocks online but can't work out which one is best for my scenario. I guess I need a two-column array (one being the sort column). Would the array function need to be behind a button on frm_Resources? Would frm_Resources need to be Unbound?

[#5]

@JohnShir 

Working out how to approach problems like this is a one-off for the most part. In fact, as an aside, one of the reasons responding to questions like this is that every situation is different in some ways, so there is no "out of the box" code that can be passed along and dropped into a module.

Arnel has demonstrated an ability to create code for specific situations, in the past. In a more generic one, it's going to be tough to do, though. If you can provide table and field names and perhaps screen shots, I'd be willing to bet a basic framework would be easier to suggest.