List Box - MultiSelect

[KC]

1) I have a field in a table called payment type which is a list box with
value list. When I added this field to my form and set MultiSelect to Simple
(also tried Extended).

My problem is that while I can select multiple items in the list, the
information is not being stored in my table.

I've tried to figure out why, but was unsuccessful. I would appreciate any
and all help.

Thank you,
KC

 

[Douglas J. Steele]

You cannot bind a field to a multiselect listbox.

Storing more than one value in a single field is actually a violation of
relational database theory, so Access doesn't allow it. What you should be
doing is creating a second table, linked to the existing table, that has a
foreign key pointing back to the primary key of the existing table, and has
one row for each payment type selected. The usual way to update this second
table would be through a subform, although there are other approaches
possible.

 

[Tom Wickerath]

Hi KC,

For a list box that allows multiselect capability, you need to iterate the
..itemsSelected property. The example shown below is for a QBF (Query by Form)
form, where I am building the WHERE portion of the SQL statement on-the-fly:

Private Function IncludeProjects() As Variant
On Error GoTo ProcError

Dim varCategory As Variant
Dim strTemp As String

strTemp = "[Project.pkProjectID] In ("

' Process all items selected in lboProjects
For Each varCategory In Me!lboProjects.ItemsSelected()
strTemp = strTemp & Me!lboProjects.ItemData(varCategory) & ", "
Next

If strTemp <> "[Project.pkProjectID] In (" Then
IncludeProjects = FinishINClause(strTemp)
Else
IncludeProjects = Null
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in IncludeProjects Function..."
Resume ExitProc
End Function

Private Function FinishINClause(strTemp As String) As String
On Error GoTo ProcError

' Strip off trailing comma and add ")"
FinishINClause = Left$(strTemp, Len(strTemp) - 2) & ")"

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in FinishINClause Function..."
Err.Clear
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[fredg]

1) I have a field in a table called payment type which is a list box with
value list. When I added this field to my form and set MultiSelect to Simple
(also tried Extended).

My problem is that while I can select multiple items in the list, the
information is not being stored in my table.

I've tried to figure out why, but was unsuccessful. I would appreciate any
and all help.

Thank you,
KC

That's by design.

A normalized database should have only one item of data in a field.

You can bind a List Box to a table field only if it's multiselect
property is set to None.

Once you set it to simple or extended, you lose the ability to save
the selections to a table field (you would need code to read which
items have been selected anyway).

 

[KC]

Thanks for replying to my question.

Now I understand why it's not storing the multiple selections.

KC

 

[Tom Wickerath]

Helen Feddema offers a tutorial on the subject. See item # 72 in this list:

http://www.helenfeddema.com/access.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________