Listbox question

You just need a little helper method that fires probably on current event when you have No New Record that goes like this

Private Sub PopulateListBox()
    For r = 0 To ListBox1.ListCount - 1
    Dim rst as DAO.Recordset
    Set rst = Me.RecordsetClone

        ListBox1.Selected(r) = rst.Fields("The Field that stores the ListBox Selection")
    Next r
    Set rst = Nothing
End Sub

Select allOpen in new window

The listbox is not bound.  If you want to see the selected items, you need to use a subform.  You can make the subform small and remove the borders so it looks more like a listbox.  That will allow you to also remove all your code.  Otherwise to do what you want, you need to create a VBA code loop to open the table with the "selected" records and loop through that recordset to set the selected property for the previously selected items.  Also, you need to make sure that you have code that deletes a record if it is deselected.

All in all, the subform is a simple no code solution that shows everything already selected.  The field in the subform should be a combo rather than list box.  The query for it should only select values that do not exist in the table you are displaying.  That way if you have a, b, c, d as options and a and c are selected, the combo will show only b and d.  You also will need to requery the combo in the current event of the form to make sure that the rowsource list always reflects only the available items.

If your field to store the list of selected items is ItemsList  and your listbox is named TestList, you can use code similar to this to save and restore the selections:

Private Sub Form_Current()

    Dim Items       As Variant
    
    Dim ItemsList   As String
    Dim Item        As Variant
    Dim Index       As Long

    ' Clear selection.
    For Each Item In Me!TestList.ItemsSelected
        Me!TestList.Selected(Item) = False
    Next
    
    ' Restore list of selected items.
    ItemsList = Nz(Me!ItemsList.Value)
    If ItemsList <> "" Then
        Items = Split(Me!ItemsList.Value, ";")
        For Index = LBound(Items) To UBound(Items)
            Me!TestList.Selected(Items(Index)) = True
        Next
    End If

    Me!TestList.Value = Null
    
End Sub

Private Sub TestList_AfterUpdate()

    Dim Items()     As Variant
    
    Dim ItemsList   As String
    Dim Item        As Variant
    Dim Index       As Long
    Dim Count       As Long
    
    Count = Me!TestList.ItemsSelected.Count
    If Count > 0 Then
        ReDim Items(Count - 1)
        For Each Item In Me.TestList.ItemsSelected
            Items(Index) = Item
            Index = Index + 1
        Next
        ItemsList = Join(Items(), ";")
    End If
    
    ' Save list of selected items.
    Me!ItemsList.Value = ItemsList

End Sub

Select allOpen in new window

Gustav,

I'm getting a type mismatch when I re-open the form that has the listbox after the selections were made the first time.  Seems to stop at:

Me!List432.Selected(Items(Index)) = True

And a strange record is being written to the table that holds the selections.  The "Code" field indicates "8;9" (without the quote marks.  8 and 9 are the key numbers of the two selections.

That field must contain either Null or a list of items:


Then the code can't fail.

John, I must not understand what to do.  I copied your code and put it in the form VBA code and it did nothing.

Pat, I understand the concept but I want the user to see the entire list and not have to select them one at a time via a combobox.

As i said you must use from the Current Event...like this

Private Sub Form_Current()
If Not Me.NewRecord Then
 PopulateListBox()
End If
End Sub

Select allOpen in new window

Of course there is the issue of the recordset fields but for this a sample data and the form is needed

Getting type mismatch on:

ListBox1.Selected(r) = rst.Fields("Code")

Here' a demo attached.
ListboxSelection.accdb

Pat, I understand the concept but I want the user to see the entire list and not have to select them one at a time via a combobox.

He still has to select them one at a time from the listbox.  I also told you the method you needed to use to display them so you could continue to use the listbox but I guess you didn't understand that part of the answer.

I strongly advise against storing multiple values in the same field.  Your original question said that selecting something from the listbox caused a record to be written to a table.   Sounds like you have changed methods.  If you want to have multiple values stored (which is a bad idea as you can see if you search) the best solution is to use the Access multi-value field.  At least no code is required.  All you have is complex queries whenever you need to obtain information for purposes other than the listbox on the form.

I do not want to store multiple values in the same field.  Let's start over.

1)  I have a form that has a listbox.  (Simple Multi-Select)  The listbox is NOT bound to the form.  It just shows the options that are available in a separate table.
2)  When the user clicks on an item in the list a record gets written to a table the form is bound to. (I have this working).
3)  When the user closes the form and comes back to it I would like the selected items to be "selected" again.   In other words, show the user which items were previously selected.
4)  Then either during the initial selection or when the user re-opens the form and if they UNselect the item, I'd like the record to be erased from the table.


That's all I want to do.

First:

I do not want to store multiple values in the same field.

Then you must have a child table with a record for each selection.

Then:

when the user re-opens the form and if they UNselect the item, I'd like the record to be erased from the table.

What record? All the child records? If so, why not mark each record as unselected?

Still, my demo has the full and exact functionality that you describe in 1) to 4), so it is a little confusing what you are after.

Gustav, I have two tables,  One is the table that the listbox gets its records from.  It is just used to display what the user sees in the listbox.  The other table is bound to the form and when the user selects a record in the listbox, a record is written to it,  I wish I knew how to explain it better.

And Gustav,  the demo you posted creates a record with, for example, 3;4 in a field.  I don't want to store multiple values in the same field.  I must be totally confused.  Sorry.

Steve,
Please go back and look at my original response.  I explained how to populate the list box.  You just have to use a recordset based on the table where the values are stored.

Gus repeated my instructions later in the thread.

And AGAIN, there is a NO CODE method available as I explained.  You just need to use a subform.

I think you are making this harder than it needs to be.  For starters, it is poor practice to insert rows into a table without filling all the fields.  What you are doing makes adding a record a two step process.  First you click the required item in the list box and second you navigate to the record that just got added to fill in the blanks.  I don't see how this is simpler for the user than just picking the option from a combo and adding the rest of the data at the same time.  What you are doing requires code and the logic to handle deletes is probably not even covered.  Access is a RAD (Rapid Application Development) tool and provides a no code solution.  You just have to live with the "Access Way" which is a continuous form or subform.

I wish I knew how to explain it better.

Certainly you can by uploading a demo database and show how do you expect the result.

I went with:  

"you must have a child table with a Yes/No field for each listbox entry and a foreign key to the table bound to the form."

I think you will find that this doesn't actually work once you move into production.

Another NO CODE solution is possible if you are willing to have the listbox show only unselected items.  In this case, the RowSource query of the Listbox joins (using a left join) to the particular subset of records bound to the form and excludes any that match.   You can build this query easily with the unmatched query wizard.

By including ALL potential selections and highlighting the options that are already selected, you have produced an interface that looks like it should delete items from the child table if you unselect them and you are not doing this.  That makes this interface confusing for the user so I would not use it.