Multi-select listbox

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

Dave the wave

I would like to add to a form a listbox (bound to a table that is separate
from the form's table) that allows multiple selections. I'm having trouble
figuring out how to implement a listbox and save the selection/s with the
record that is current.
Any help would be greatly appreciated.
 
I would like to add to a form a listbox (bound to a table that is separate
from the form's table) that allows multiple selections. I'm having trouble
figuring out how to implement a listbox and save the selection/s with the
record that is current.
Any help would be greatly appreciated.
Click to expand...

How do you want to save them? Do you want to store multiple values in
a single table field? If so... that's VERY bad design. Please explain
the table structure and where you want these values stored.
 
The application is for shipping hazardous materials. There is a list of
placards that trucks must display when hauling certain materials. Some
materials require more than one placard. Even though the list of placards is
rather static I thought it better to allow for future modifications to the
placard list by the user. So, I created a separate table to handle the list
of placards.

I envisioned a list box showing filled with data from the placard table
which would allow the user to select any and all applicable placards. I have
yet to figure out how to implement this. I can't seem to find information
regarding binding a listbox to a table. It seems the common application for
list boxes is as a filter and not as a means of entering data.

I thought about building another table to store which placards are to be
associated with each material, but I'm not sure how listboxes are used to
update records. Alternatively, I've considered to creating a function that
will "read" the selections and built a custom string -store in the material
table- that I would also have to "interpret" each time the record was
accessed.
 
My use of the listbox is different than described in the web page linked
below. I am trying to use a listbox to enter data into a table. The link
describes reading the selections for use as a filter or for building an SQL
statement.

Please see my reply to Mr. Vinson's comments for a more detailed description
of what I am trying to do.

Thank you,
David G.
 
Hi,
I've done this using your last idea, storing a string and interpreting it
when you open the form later -

when you update the multi-select listbox r06c3: (me!r06label is another text
field in same table) - only works with less than 100 items, else change
formatting

Private Sub r06c3_AfterUpdate()
Dim e, itm
Me!r06Label = Null
For Each itm In Me!r06c3.ItemsSelected
Me!r06Label = Me!r06Label & Format(Me!r06c3.Column(0, itm), "00")
Next
End Sub

When you come back to the form later,

Private Sub Form_Open(Cancel As Integer)
Dim x, y
'put multi-select choices back into list boxes (stored in "r0yLabel" fields)
For y = 2 To 6
For x = 0 To Me("r0" & y & "c3").ListCount - 1
If InStr(1, Me("r0" & y & "Label"), Format$(Me("r0" & y &
"c3").Column(0, x))) Then
Me("r0" & y & "c3").Selected(x) = True
End If
Next
Next
End Sub

HTH,
John
 
I thought about building another table to store which placards are to be
associated with each material, but I'm not sure how listboxes are used to
update records.
Click to expand...

Since you have a perfectly classic Many (trucks) to Many (placards)
relationship, I'd strongly suggest that you do in fact create such a
table - with the TruckID and the PlacardID as probably the only
fields.

A Subform with a combo box based on the Placard table would let you
very easily go down a list selecting placards. If you prefer to use a
listbox (which in this case seems very reasonable), you'll need some
VBA code to add the records to the table. Here's some code that I
filched from one of my databases; you'll need to adapt it to your
particular situation. The user would select health issues (placards,
in your case) from the listbox lstHealthIssues and then click the
Process button to execute this code.

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
 
Back
Top