How to populate listbox with multiple values from a table

[rocco]

I am trying to populate a listbox with values from a master table.

1. User selects multiple selection from listbox and clicks save.
The values will be save to master table: Tbl_Main_Clinical field: CL_resp_consult
The values will be save to the table field as - sample1/sample2/sample3
I am using code StrSql = "INSERT INTO... to do this and it works great.

2. In my update form I would like to have the same list box with the selections.
BUT what I am looking for is the 3 samples to be highlighted as how the user selected them in the original entry.
Using this form they can unhighlight and select maybe some other entry if needed.


I have created the same listbox with the same list selection as the originals one.

Control Source: Cl_resp_consult - this points to the master table that has the values.
Row Source: This is just the SELECT from the table that gives me the group that makes up the list selection for the users.

Looked at all the other variables in the property sheet but not sure what to do.


Need some ideas and guidance please.

 

[dhookom]

I'm confused by your question. When you state "The values will be save to the table field as - sample1/sample2/sample3" are the values saved to:
[ul]
[li]Three different records[/li]
[li]One field in one record[/li]
[li]Three fields in one record[/li]
[/ul]
I hope three records.

Maybe if you shared your code "I am using code StrSql = "INSERT INTO... to do this and it works great" we would understand better.

My understanding is you would like to have a list box displaz the saved values.

Duane
Hook'D on Access
MS Access MVP

 

[Darrylles]

Silly to do it, but here's my guess:

1) This is a 'staged' process.
2) One form enables a user to identify X items from a list.
3) These are 'stored' in a seperate table.
4) A different 'update' form pulls these 'selections'.
5) This update form enables editing of this list, but must display the pre-selected items highlighted within the full list.

My questions would be:

A) Why is this 'staged', why have 2 forms that basically do the same thing?
B) How do you identify items selected by different users?
   User 1 may select 3.
   User 2 may select 5 different items.
   There are now 8 items in the 'selected' master table.

   On your 'update' form - how do you identify which 'user selected' list to display in order to 'update'?

It seems that you wish a user to identify some items tentatively, then, you wish them to confirm, but also be able to amend their initial tentative selections.
It also seems that in reality - all you need is code to highlight individual items in a listbox - is that right?

Better that you first describe your business rules.
What exactly are you trying to do (in English - not code)?

 

[MajP]

To hilite the values in a multiselect listbox, you have to do a few things
1. Clear out any old selections
2. Find the subset of records to hilite and create a recordset of those items
Create a nested loop
3. Loop the recordset of items to hilite
4. loop all the items in the listbox to find a match
5. If there is a match select it.
All techniques are some version of this.

Here is an example. Suppliers provide many products. If you pick a supplier from the combo it show the products that they provide by hiliting the listbox.

Private Sub cmboSupplier_AfterUpdate()
  ClearSelections Me.lstProducts
  ShowSelections Me.lstProducts, Me.cmboSupplier.Value
End Sub

Public Sub ShowSelections(lst As Access.ListBox, ForeignKey As Long)
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim varItm As Variant
  Dim SelectedID As Long
  Dim I As Integer
  'Need to get a recordset of the items to hilite in the listbox
  strSql = "Select ProductID from Products where SupplierID = " & ForeignKey
  Set RS = CurrentDb.OpenRecordset(strSql)
  'loop the recordset
  Do While Not RS.EOF
    SelectedID = RS!productID
    'loop the items in the listbox to find a match
    For I = 0 To (lst.ListCount - 1)
      Debug.Print lst.Column(0, I) & " " & SelectedID
      If lst.Column(0, I) = SelectedID Then
        lst.Selected(I) = True
      End If
    Next I
    RS.MoveNext
  Loop
End Sub
Public Sub ClearSelections(lst As Access.ListBox)
  Dim I As Integer
  For I = 0 To (lst.ListCount - 1)
     lst.Selected(I) = False
   Next I
End Sub

Normally I would then also write code so that when you pick a new selection it does an append query to add the selection to the table, if you uncheck it would do a delete query to remove a selection. That code is not provided with this demo.

 

[rocco]

My apologies for the confusion.
I have set up Form1 to have a listbox that is able to select multiple entries.
So, if the listbox returns 10 items then the user is able to select/highlight 3 items (Sample1/Sample2/Sample3)
When I save the data to my master table the field with this data for the listbox will have - Sample1/Sample2/Sample3

My question is when I try to do and update in Form2.
Same as Form1, I would like to have thelistbox return 10 items that the user is able to do a multi selection. (I know how to do this part)
However, my challenge is how do I highlight what was saved before? In this sample I saved 3 things which I would like to have it highlighted automatically for the user. The user can then go ahead and unhighlight it and select 5 other things or just one.



Thank you MaJP for the code I will update the form and see if your suggestion works.

 

[dhookom]

You didn't specifically answer my question, provide any code, or provide any table and fields.

Please consider giving us more information.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

This is a more complete demo.
It shows how to hilite the multiselect listbox based on records stored in a table.
Shows how to add a new record to a table based on a selection in the multiselect listbox
Shows how to delete a record from the table when you unselect the selection. Just scroll through the records in the main form.

Private Sub Form_Current()
 ' MsgBox "The On Current event. Clear the selections then hilite the selections."
  ClearSelections Me.lstProducts
  If Not Me.NewRecord Then
    ShowSelections Me.lstProducts, Me.OrderID
  End If
End Sub
Public Sub ShowSelections(lst As Access.ListBox, ForeignKey As Long)
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim varItm As Variant
  Dim SelectedID As Long
  Dim i As Integer
  'Need to get a recordset of the items to hilite in the listbox
  strSql = "Select ProductID from qryOrders_Products where OrderID = " & ForeignKey
  Set RS = CurrentDb.OpenRecordset(strSql)
  'loop the recordset
  Do While Not RS.EOF
    SelectedID = RS!ProductID
    'loop the items in the listbox to find a match
    For i = 0 To (lst.ListCount - 1)
      Debug.Print lst.Column(0, i) & " " & SelectedID
      If lst.Column(0, i) = SelectedID Then
        lst.Selected(i) = True
        Exit For
      End If
    Next i
    RS.MoveNext
  Loop
End Sub
Public Sub ClearSelections(lst As Access.ListBox)
  Dim i As Integer
  For i = 0 To (lst.ListCount - 1)
     lst.Selected(i) = False
   Next i
End Sub

Private Sub lstProducts_AfterUpdate()
  If Not IsNull(Me.OrderID) Then
    AddRemoveSelections Me.OrderID
    Me.SubFrmOrderProducts.Form.Requery
  Else
    MsgBox "Create the order first by giving it an order date."
  End If
End Sub
Public Sub AddRemoveSelections(OrderID As Long)
  Dim lst As Access.ListBox
  Dim RS As DAO.Recordset
  Dim FocusedItemIndex As Integer
  Dim strSql As String
  Dim ProductID As Long
  Set lst = Me.lstProducts
  FocusedItemIndex = lst.ListIndex
  ProductID = lst.Column(0, FocusedItemIndex)
  'The selected property is an array of all items and holds a true false if the item is selected
   If lst.Selected(FocusedItemIndex) Then
     'If the item is focused and selected then add it.Add to list
     strSql = "Insert INTO tblOrder_Products (OrderID, ProductID) VALUES (" & OrderID & ", " & ProductID & ")"
   Else
     'If the item is focused but not selected then delete it
     strSql = "DELETE * FROM tblOrder_Products where OrderID = " & OrderID & " AND ProductID = " & ProductID
   End If
   Debug.Print strSql
   CurrentDb.Execute strSql
End Sub