save selections of an unbound listbox

[Swamphen]

Hi there.

I'm still a total newbie in Access and VBS, so don't get mad at my stupidity:

- I have a form "StructureInput" which is coupled with a table "Structure".
- In the form I have some listboxes with "multiple select" turned on.

So far, no problems.

Now, because I didn't fully understand the coupling between multi-valued fields in a table and corresponding multi-select listboxes I had this brilliant (NOT) idea to make my listboxes in StructureInput UNBOUND and to have an "afterupdate-eventprocedure" which assigns a unique number to the corresponding field in Structure, formed by multiplying prime numbers associated with each possible selection in the listbox.

FIRST A QUESTION: Is this last strategy total nonsense or is it workable?

AND THEN MY PROBLEM (or call it annoyance): when I open my form the selections in the unbound listboxes are gone. After reading other threads I know I must save my form on exit, but how exactly should I do this?

Thanks very much,

Swamphen

 

[JerryDennison]

I answered this question on A3 but now having read it here (you've worded it considerably different) my response on A3 was incorrect. You SHOULD NOT store this value in your db. In fact, your structure is incorrect. What you're trying to do is store multiple values in a field seperated by a delimiter (you're simply trying to get around this by storing a calculated result that could get you back to this point). Fields should be atomic. One value per field. If you need to store multiple values you should move this into it's own table and store them as individual records linked back to the primary entity.

 

[Swamphen]

It's a good thing I don't believe in copy/paste apparently.

I kinda thought this would be the answer given. And yes, maybe I should make the fields atomic.

But lets get concrete. This is an example of data I have to work with:

RIVERBANKMATERIAL

Left Right
possibility of further growth x x
further growth is not possible x x
concrete x x
steady attached rocks x x
steel profileelements x x
fabricated profileelements x x
wooden poles x x
tar x x
concrete grow tiles x x
geotextile x x
rubble x x

I made a multiselect listbox for each riverside and when for instance "concrete" and "tar" were selected, 7*23=161 was saved as value in the LeftMaterial variable.

Now, when I make a variable for each option, should I then make radiobuttons for each option, or can I keep my listbox and attach each option in the listbox with a certain atomic variable?

Thanks a lot,

Swamphen

 

[JerryDennison]

This definetly belongs in it's own table. Not as seperate fields in a single table. You would be doing yourself a big favor by normalizing the data.