I am trying to take a user-selection from Listbox1 (multi-select enabled) to populate Listbox2 (no user selection in Listbox2). Listbox1 has 3 columns, and is displaying column(1). I want Listbox2 to display column(2) from Listbox1 user selections.

I have two tables: Summary_Data & Vendor_Parts I have one query: Vendor_PN_Query that is set to use a combo box selection of Vendor to filter to a specific vendor's list of PN's. (Cbo_Vendor) Column(0)=Vendor, Column(1)=PN, Column(2)=Pgm_Name

Cbo_Vendor selection saves to Summary_Data table field Vendor PN selection from Listbox1 saves to Summary_Data table field PN I need the data from Listbox2 to save to Summary_Data table field Pgm

Listbox1 shows PN field from Vendor_PN_Query, based on Cbo_Vendor user selection. User can select single or multiple PN's from this listbox by adding the checkmarks. Listbox2 is supposed to show the Pgm_Name(s) that is/are associated with the selected PN(s), and then record that information back into the Summary_Data table under the Pgm field.

I cannot find any code examples that actually work for this. When I was able to code to retrieve some data, I got a repeat of the vendor name (twice for each PN selected), and that was all, regardless of any column count or row source variations I used in my code.

Here is my current basic code, but his does not retrieve any data for Listbox2:

Private Sub Cbo_Vendor_AfterUpdate()    
    Me.Listbox1.Requery        
End Sub

Private Sub Cmd_Done_Click()

   Dim i As Integer    'Row Index

   If Listbox1.ListCount = 0 Then
       MsgBox "Please select at least one part number."
       Exit Sub
   End If

   For i = 0 To Listbox1.ListCount - 1
      If Listbox1.Selected(i) = True Then
          Listbox2.AddItem Listbox1.Selected(i)
      End If
   Next i

End Sub

Modified code based on http://www.iaccessworld.com/29540-2/, but I still do not retrieve any results in listbox2.

Private Sub Cbo_Vendor_AfterUpdate()

   Me.Listbox1.Requery

End Sub

Private Sub Cmd_Done_Click()

'Transfer column 2 of selected items in listbox1 to listbox2

Dim i As Integer

  With Me.Listbox1

    For i = 0 To .ListCount - 1
       Me.Listbox2.AddItem (Selected.Column(2, i))
    Next i

End With

End Sub

I am finding examples online for everything from simple code like this to having to create class modules and temporary tables. Is it really this simple and I am missing something?

I finally have code that populates Listbox2 (although it populates in reverse order - not a huge deal). However, now I need to auto-select all items in Listbox2 and automatically save them to my table. The field in the table is set to value list and allows multiple values.

Here is my working code:

Dim i As Integer

For i = Listbox1.ListCount - 1 To 0 Step -1
  If Listbox1.Selected(i) = True Then
    Listbox2.AddItem Listbox1.Column(3, i)
  End If
Next I

Working Code:

Private Sub Cbo_Vendor_AfterUpdate()
   Me.Listbox1.Requery
End Sub

Private Sub Cmd_Done_Click()

'Transfer column 3 of selected items in listbox1 to listbox2
Dim i As Integer

For i = Listbox1.ListCount - 1 To 0 Step -1    'Code works but lists programs in reverse order & does not save to table.
  If Listbox1.Selected(i) = True Then
    Listbox2.AddItem Listbox1.Column(3, i)
  End If
Next I

End Sub

Private Sub Cmd_Save_Click()
    If Me.Dirty Then
        Me.Dirty = False
    End If
End Sub

1 Answers

1
Lisa On

I have code that now works to transfer selected items from Listbox1 to Listbox 2, without duplicates, and then saves to Table:

Private Sub Cmd_Done_Click()

'Transfer column 3 of selected items in listbox1 to listbox2
Dim i As Integer
Dim j As Integer
Dim strBlock As String
Dim x As Integer
Dim bool As Boolean

For j = Listbox2.ListCount - 1 To 0 Step -1
    Listbox2.Selected(i) = False
Next j

Me.Listbox2.RowSource = ""

For i = Listbox1.ListCount - 1 To 0 Step -1

    strBlock = Me.Listbox1.Column(3, i)

    If Listbox2.ListCount = 0 And strBlock <> "" Then
        If Listbox1.Selected(i) = True Then
          Listbox2.AddItem (strBlock)
          Listbox2.Selected(0) = True
        End If
    Else
        bool = False
         For x = Listbox2.ListCount - 1 To 0 Step -1
            If Listbox2.ItemData(x) <> strBlock Then
               If Listbox2.ItemData(x - 1) <> strBlock Then
                  bool = False
                  If Listbox1.Selected(i) = True Then
                     Listbox2.AddItem (strBlock)
                     Listbox2.Selected(x + 1) = True
                     Exit For
                  End If
               End If
            Else
                bool = True
                Exit For
            End If
        Next x
        End If
Next i
End Sub