Populate textboxes with multi-select Listbox Items

[Wren]

On my form there are 2 listboxes, the second one's Row
Source is dependent on the selection made in the first one.

The second is a multi-select listbox and I'd like to
populate 5 text fields based on the users listbox
selections.

Users may only select 2 items, populate textboxes, then go
back to the first listbox and choose another category,
then choose other results from the 2nd listbox to add to
the text boxes.

This was working nicely before I enabled multiselect on
the 2nd listbox. Then the textboxes would all populate
with the last listbox item selected.

I'm using a For / Next loop to go through the
ItemsSelected. Once the user is done populating the text
boxes from the Listbox items, those 5 textbox values will
be used at a later point for insertion into a table.

How would you suggest testing to see if the textbox is
already populated with another piece of data (so it needs
to be null or = ""), AND also make sure that the same
Listbox.Column value wasn't already used in another of the
5 textboxes?

Or any other suggestion on a different approach to achieve
the same results?

 

[Marshall Barton]

On my form there are 2 listboxes, the second one's Row
Source is dependent on the selection made in the first one.

The second is a multi-select listbox and I'd like to
populate 5 text fields based on the users listbox
selections.

Users may only select 2 items, populate textboxes, then go
back to the first listbox and choose another category,
then choose other results from the 2nd listbox to add to
the text boxes.

This was working nicely before I enabled multiselect on
the 2nd listbox. Then the textboxes would all populate
with the last listbox item selected.

I'm using a For / Next loop to go through the
ItemsSelected. Once the user is done populating the text
boxes from the Listbox items, those 5 textbox values will
be used at a later point for insertion into a table.

How would you suggest testing to see if the textbox is
already populated with another piece of data (so it needs
to be null or = ""), AND also make sure that the same
Listbox.Column value wasn't already used in another of the
5 textboxes?

Or any other suggestion on a different approach to achieve
the same results?

It sounds like the result you want is a violations of the
rules of database normalization - each field in a table
shall have one and only one value (i.e. not a list).

You should take a step back and (re)design your tables so
that you have multiple rows in a separate table instead of a
list of values in a single field.

 

[Wren]

Good afternoon Marshall, I appreciate your suggestion.
However, I don't believe that normalization is really the
issue here. The issue is the same whether there are one
or two listboxes involved.

More specifically, the listbox details skills an employee
might have. He is permitted to add 5 to his saved
profile. He might choose "finance", "accounting",
and "math" from the listbox (as a multiselect). I need
these 3 values to then populate the textboxes in order to
save them to his profile.

In the process of assigning those selected values to the
textboxes, I think the best thing to do would be to find
a way to also check:

1) whether the user might have selected "math" twice for
example (since they might revist the listbox and add more
or change their selections)
2) it should not overwrite text boxes that are already
populated (there is a separate clear function for each
text box)

 

[Marshall Barton]

Good afternoon Marshall, I appreciate your suggestion.
However, I don't believe that normalization is really the
issue here. The issue is the same whether there are one
or two listboxes involved.

The list boxes are just a user inteface technique. The
issue is that you want the Employee table to have a list of
skills in a sequence of fields like Skill1, Skill2, ...,
which is clearly against the normalization rules.

More specifically, the listbox details skills an employee
might have. He is permitted to add 5 to his saved
profile. He might choose "finance", "accounting",
and "math" from the listbox (as a multiselect). I need
these 3 values to then populate the textboxes in order to
save them to his profile.

Proper normalization calls for you to have an EmployeeSkills
table with at least two columns, one for the employee ID and
another that identifys a single skill. If the table's
primary key consisted of both fields, then adding a skill
twice would not be permitted and you wouldn't have to do
anything special. Adding a new skill would just be a matter
of adding a record to the table. No muss, no fuss.

In the process of assigning those selected values to the
textboxes, I think the best thing to do would be to find
a way to also check:

1) whether the user might have selected "math" twice for
example (since they might revist the listbox and add more
or change their selections)

2) it should not overwrite text boxes that are already
populated (there is a separate clear function for each
text box)

I'm sure this can be done using a bunch of code, maybe using
something like this air code:

If Not (Me.listbox.ItemSelected(varItem) = Me.textbox1 _
OR Me.listbox.ItemSelected(varItem) = Me.textbox2 _
OR Me.listbox.ItemSelected(varItem) = Me.textbox3 _
OR Me.listbox.ItemSelected(varItem) = Me.textbox4 _
OR Me.listbox.ItemSelected(varItem) = Me.textbox5) _
Then 'find an empty textbox
If IsNull(Me.textbox1) Then
Me.textbox1 = Me.listbox.ItemSelected(varItem
ElseIf IsNull(Me.textbox2) Then
Me.textbox2 = Me.listbox.ItemSelected(varItem
. . .
End If
End If