Create Multi-Select List box

[Guest]

How on earth do I create a multi-select list box?

I created a form, frmEditStudent. Bound to a table, tblStudent. the form
is to record modifications that a student should receive according to several
meetings that parents/administrators have. The fields MathMods, ElaMods,
SciMods, and SsMods will have multiple selections and their field type is
number.

I created a listbox using the toolbox with a record source from tblMods and
bound it to MathMods. I set the multi-select to extended.

When I open the form and attempt to select multiple choices, no banana.
Only 1 is highlighted and the value is not stored at all.

Any help would be greatly appreciated.

 

[John W. Vinson]

How on earth do I create a multi-select list box?

I created a form, frmEditStudent. Bound to a table, tblStudent. the form
is to record modifications that a student should receive according to several
meetings that parents/administrators have. The fields MathMods, ElaMods,
SciMods, and SsMods will have multiple selections and their field type is
number.

I created a listbox using the toolbox with a record source from tblMods and
bound it to MathMods. I set the multi-select to extended.

When I open the form and attempt to select multiple choices, no banana.
Only 1 is highlighted and the value is not stored at all.

Any help would be greatly appreciated.

A Listbox control has one and only one Control Source. You will need *another
table* to store the multiple values - you cannot store multiple numeric values
in a single field. Fields are atomic, and have one and only one value!

Don't confuse FORMS - which display and allow you to edit data - with TABLES.
Only Tables store data for later use. You need to get some more tables set up
before you start working on forms.

John W. Vinson [MVP]

 

[Guest]

So why offer a multi-select option if you can't use it without other tables.
So I would need to create a table, call it tblMathMods, and store all the
math mods in that table with a child/parent link of the students ID# in a sub
form. Then I can reporduce that information on reports as necessary.

What kind of coding do I need to use the listbox to write multiple records
to the table or would it be better to scrap the listbox and just use
continuous forms in the subform?

 

[John W. Vinson]

What kind of coding do I need to use the listbox to write multiple records
to the table or would it be better to scrap the listbox and just use
continuous forms in the subform?

Continuous forms are easy, and require no code. If you DO want the
multiselect, here's some sample code you could adapt.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]