Post Multi-Select Listbox data to field

[netsense]

Hi all



I am trying to work out how to post the values selected in a multi-select listbox to the relevant field, hopefully comma-delimited.

I have created the listbox, added the row source data and set the default value. At present, the control name is List39, the "Control Source" is set the field name ("PossibleLandscapingRoles"), but all the reading I have done seems to require one of many variations of macro code. I am trying to avoid coding, as I have almost no background in it, and so I have no idea of what would need to change in the code to adapt it to my needs, or even which code variation would work and which would not.

Is anyone able to help?

Thanks in advance

[Micron]

If you want to put one selection in a table somewhere, you can bind the listbox to a field (set its controlsource) but you cannot do that with a multi select listbox. You are stuck with code or maybe it can be done with a macro (a macro in Access is not the same as a macro in Excel). Access macros are limited in what they can do, plus when they error, they usually just die. So for the most part, experienced developers don't use them thus don't know a whole lot about them.

You might start by looking for code that loops over selected items in a listbox (ms Access loop over selected listbox items)
Your code would have to include building a cs string, which might be a bit hard to find because it would be a rare case where having comma separated values in one field would be a good idea.
When/if you get stuck, post what you've tried. Please enclose posted code within code tags (use # button on posting toolbar) to maintain indentation and readability.

EDIT - maybe not so hard. Took about 20 seconds but maybe not the best out there.
https://learn.microsoft.com/en-us/of...ction-list-box

[netsense]

Hi Micron
I found that Microsoft link, and has a good look at it. With my level of knowledge of coding, however, I don't know what bits to change for my database. I don't seem to have the Northwind sample database on my 365 installation, so I have no idea which bits of the code refer to fields or control or whatever, and which are functions within Access.

[Micron]

Yeah, that one looks kinda lame. I saw the code and figured it would lead you somewhere. It's midnight here so I'm signing off for the night.
Maybe you could explain why you want csv in a table field just in case it turns out to be a bad idea. Such fields are hard to query and usually the data doesn't look great as csv's in a form or report. In some 20+ years, I've only needed it once.

[netsense]

Oh, I assumed csv would be easy to query. I would prefer to have all possible landscaping roles that a plant could fill in the same field. But it's beginning to seem like multiple fields would be a simpler option.
Thanks anyway, and have a good night!

[Micron]

Might be a good idea to post info regarding the purpose of the db, or at least the tables involved. Multiple fields sounds like repeating like fields, which is just another type of mistake Excel users tend to make. Each "role" should be a record, not a field.

[netsense]

The purpose is to classify a range of plant species as to their potential roles in re-landscaping a property, and for the landscape designer to be able to search for and select suitable species for an area. There is currently one table for this purpose, and seven roles:
Feature tree
Flower bed plant
Grasses & similar

Ground cover
Mass planting shrub
Screen planting/Hedge

Wet area/Rain garden plant

[Gicu]

As already mentioned by Micron, you should add a new table having PlantID as a foreign key and LandscapeRole as a field (plus its own autonumber primary key). Then you would simply replace your listbox on the main form with a subform based on the new table, bind the LandscapeRole to a combo box with the same row source as the list box and make sure you link the main form\subform on PlantID. Now you can add your roles in a proper db structure....

Cheers,

[netsense]

Hi Vlad
Thanks for that. That's probably the right approach from a database viewpoint, but from my viewpoint as the guy who's doing all the work, way beyond my competence.

[Micron]

There's lots of help available here and elsewhere. If you can't do this correctly on your own, you're never going to be able to get around the barricades that you yourself will create. Best to do it right. So in terms of hierarchy, this isn't like family / genus / species, this is "I have this shady spot (or whatever) so what is proven to work well in such spots? That may be too simple but it should serve to point out that this is more like parent/child relationships that you determine. It does not have to be like
great-grandfather, grandfather, father, child but it can be.

What you'd need to do is define what is subordinate to what. I don't know enough about landscaping and the exact type of tool you want to build here. It may be that you are not concerned with classifications such as genus. However, it seems you might have at least a table for
- roles (shade, erosion prevention, food, aesthetics, insect support, etc.) and plants that are suitable for those roles (in related order to those just for discussion):
- genus (tree, grasses, cucumber, flowers, lavender for bees, etc.).
It may be that you need species table as well - deciduous, fruit, evergreen (for trees).

In the end, it may just be that all you want is what should I plant here, so it's location vs suitability. However, I imagine suitability is dictated by problem solving, such as erosion prevention. So maybe still 3 tables, but less scientific that genus/species stuff. Sorry if I got carried away there. Just giving you food for thought.

I don't suppose this db will do any work order/task/invoicing/etc. management?