Selecting and storing the results of a multi-select list box

[Guest]

Is there a way to concatonate together the entries selected from a list box
and store them in a text field? I have found loads of suggested ways to run
reports but nothing about just storing the values.

The database I'm building has a small support table called "Q1" and this
holds a "Code" and "Codetxt" field both of which are text. I have a question
table that will store the results of questions asked, but a couple of the
fields need to allow multiple selections.

I can build the list box to be populated by the support table and what I
want to do is update the relevant field in the question table "Q1ans" with
the selections made.

Corin

 

[Douglas J. Steele]

Well, storing multiple values in a single field is a violation of database
normalization principles, so I'm not surprised that you're having problems
finding anything about it.

Something like the following untested air-code should return a
comma-separated list of the selected values in the given list box:

Function SelectedItems(WhichList As ListBox) As Variant
Dim strSelected As String
Dim varItem As Variant

If WhichList.ItemsSelected.count = 0 Then
SelectedItems = Null
Else
For Each varItem In WhichList.ItemsSelected
strSelected = strSelected & WhichList.ItemData(varItem) & ", "
Next varItem
If Len(strSelected) > 0 Then
strSelected = Left$(strSelected, Len(strSelected) - 2)
End If
SelectedItems = strSelected
End If

End Function

You'd use this function to assign a value to a text box bound to the field
in which you want to store the values. You'd have to do this assignment in
VBA code, best the form's BeforeUpdate event, since you cannot use a
function as the text box's ControlSource and have the value stored both.

 

[Keith Wilby]

strSelected = Left$(strSelected, Len(strSelected) - 2)

Doug, what is the purpose of the "$" sign in this line? I would have
written it as

strSelected = Left(strSelected, Len(strSelected) - 2)

but then I'm not an MVP!

Thanks.

Keith.

 

[Douglas J. Steele]

Old habits die hard.... <g>

There's a very subtle difference between Left and Left$ that really doesn't
make much difference: either would work in this case.

Left$ will not accept a Null value, but we already know that there's a
non-null value in strSelected, so we're safe. Since it doesn't have to do as
much, Left$ will be infinitesimally quicker than Left, but you'd never
notice the difference. Many of the string functions have the two versions.

 

[Keith Wilby]

Old habits die hard.... <g>

There's a very subtle difference between Left and Left$ that really
doesn't make much difference: either would work in this case.

Left$ will not accept a Null value, but we already know that there's a
non-null value in strSelected, so we're safe. Since it doesn't have to do
as much, Left$ will be infinitesimally quicker than Left, but you'd never
notice the difference. Many of the string functions have the two versions.

Duly noted, thanks Doug.

 

[Guest]

I wouldn't be doing this but the client has a system we have to interface
with and thats the way they do it...

I assume that "WhichList" is the name of the List box to be referenced, but
which part idcentifies the target field to store in?

yes you are talking to a complete VBA beginner...

Thanks in advance

Corin

 

[Douglas J. Steele]

WhichList is a variable used in the function. You do not need to change
that.

To use the function, you'd put something like:

Me.MyTextBox = SelectedItems(Me.MyListBox)

in your VBA. (Replace MyTextBox and MyListBox with the names of your actual
controls)

 

[Guest]

Doug,

Thanks for this, I have it working now....

As an aside is it possible to pick up the "unbound" column of a multi select
box? or is only the bound one available?

Regards
Corin