Multiple answers in a form

[AccessIlliterate]

I have a list box in a form and have the Multi Select property set to
Extended. When I switch to a new record the selected items in the list box
disappear from the previous record. How can I get these choices to remain?

 

[Jeff Boyce]

Is your listbox bound to anything? How does Access "know" that the items
selected in the listbox are supposed to stay connected to the "old" record?

(in other words, please describe the underlying data structure -- "how"
depends on "what")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AccessIlliterate]

The list box is bound to a table that contains only the items listed in the
list box. The entire form is connected to a table that store all of the info
from this form.I would like to be able to choose multiple items in the list
box and be able to run a query to extra this information.

 

[Douglas J. Steele]

What version of Access? Prior to Access 2007, it was not possible to bind a
multi-select list box to a field: the multi-select list box will always
indicate Null, regardless of how many items are selected.

 

[Jeff Boyce]

The listbox derives its contents from the table, check!

But how does Access know which items were selected?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AccessIlliterate]

I am using a 2000 File format of Access. Do you know what use there is for
the multi-select property if it returns a Null?

 

[AccessIlliterate]

Jeff--Thanks for your help. I guess that is what I am asking, how does
access remember what I am selecting? After selecting the 2 or 3 items I
want, how do I tell Access that I want these items related to this record?
Someone else has told me that no matter how many I pick, it will return Null
in this field.

 

[Douglas J. Steele]

Multi-select list boxes (at least, in Access 2003 and earlier) really were
only intended to be used unbound, and you'd use VBA code to work with the
selected items. For example, you could have a multi-select list box of, say,
customer ids, and use code like this to open a form that only displayed data
for those specific customers:

Dim strWhere As String
Dim varSelected As Variant

With Me!MyListbox
If .ItemsSelected.Count > 0 Then
For Each varSelected In My!MyListbox.ItemsSelected
strWhere = strWhere & "'" & Me!MyListbox.ItemData(varSelected) & "',
"
Next varSelected
strWhere = "CustomerID In (" & Left$(strWhere, Len(strWhere) - 2) &
")"
End If
End With

DoCmd.OpenForm "MyForm", acNormal, , strWhere

 

[Jeff Boyce]

Doug offers an approach to recording which were picked.

Regards

Jeff Boyce
Microsoft Office/Access MVP