multiple selection on listbox???

[Jim Tanis]

Please help, I'm on fire!!!

I have a table (tbl1) with the following fields:
ID, name

I lookup that table to get values for a listbox that stores attendees for a
meeting. The issue here is that i can't make this damn listbox to work with
multiple selection.

I want whenever a multiple selection is made, the selected items for the
record to be saved so when I open the form it will display the names i had
selected (from tbl1).

I thought this could be done in the following day (theory works!):
Whenever a multiple selection is made, a new record is added to tbl1 that
sums the names of the attendees. Ok, saving is solved but recalling the
multiple selections???
After 2 days of thought, I worked this around too. I changed the ID column
from autonumber to integer and wrote code, that uses the PARITY BIT
calculation method to give the sole names their ID. aka 1,2,4,8,16,32,64,128
... (2^n where n=1,2,3 etc) and so on . Then, when a multiple selection was
made the ids were summed to give another unique number that is between the
powers of 2. aka if selection was records with IDs 1,2,8 --> the selection
record was given the id 11. Because each sum that is not a power of 2 is
given by only a the sum of powers of 2, issue solved. Or so I thought...
Access'es floating point calculation limit comes here, were it allows me to
have only 31 sole (power of 2) records. Zeros after 31st.

So we're back to zero!!
Q1: Is there another way to do that??? (prime numbers would work just as
well, but there is no way to calculate them )
Q2: I can't figure out how to populate and recall the multiple selected IDs
to use in an append query (to add that "multiple selection" record". A code
snippet would be mostly appreciated (in other posts it's way to specific)
Q3: What happens if in another table the multiple selection made, is prone
to changes? (ID for the selection is recalculated, thus a new "multiple
selection" ID has to be created, but the old one can't be deleted, because it
may be used in another record.
Q4: In the previous case how is the name field changed?? (either if another
selection is added or deleted)

Thanx for any feedback!

 

[Wayne Morgan]

You need to save the multiple selection in a linking table. You essentially
have a many-to-many relationship. You can make multiple selections from the
list box for each of multiple records on the form. Each selection in the
listbox will create one line in the linking table. You will need the ID
field of the selection and the ID field of the current record on the form
for each line in the linking table.

 

[Jim Tanis]

Wonderfull idea for most of the selections. In fact I've been using it for
some of the tables I'd finished the structure (Can't forgive myself not
remembering it).
But, what do I do with some lookups that have 1-10 records to select from?
Maintaining one more table for every lookup is kind of painfull.
What do you think?

 

[Wayne Morgan]

Yes, it's a little painful, but not as painful as trying to store this some
other way. The problem with combining the data to store it as a single unit
is that you have to combine it in such a way that it can be decombined
again. You also have to allow for changes in that data over time.

 

[Jim Tanis]

You convinced me. But is there a way, other than form/subform, to use on a
form? I think my users are well seated using the combo box I used to have
before the necessity for multiple selections arouse.
In that way as I thought of it it would require a multipage tab to set one
page to each lookup.

 

[Wayne Morgan]

A subform and a listbox are the only two ways I can think of to easily
display multiple associated records. If you need to edit the data in those
records as well, then you would need a subform.

 

[Jim Tanis]

I think because the users are prone to careless clicking, i'd go with the
listbox solution. Could you give me a code snippet to show me how to make it
work? Trying it with simple selections was a traumatizing experience for me.

Thanx in advance.

 

[Wayne Morgan]

You have to run through the ItemsSelected property of the listbox. Here is
an example adding selected items to a table.

Example:
Dim varItem As Variant
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblMyTable", dbOpenDynaset)
For Each varItem in Me.lstMyListbox.ItemsSelected
With rst
.AddNew
!fldMyField = Me.lstMyListbox(varItem)
!fldFormRecordID = Me.txtID
.Update
End With
Next
rst.Close
Set rst = Nothing
Set db = Nothing

Before doing the above (since it only adds items) you would need to run a
Delete Query to delete all associated records for the current form record
then add these. That way you only have the latest changes. You could set a
flag when you "dirty" the listbox so that you only do this if changes have
been made.

In the form's Current event, you would need to open a query that was
filtered on the form's ID field. This query would pull records form the
tblMyTable mentioned above. You would then need to go through each item in
the listbox and if it matched an item in the query, highlight that item in
the listbox.

Essentially, you are taking over all of the functionality of a subform and
doing it yourself through code. You may actually find a subform with a
checkbox field to select the desired records easier to do.