Pass multi-selctions from ListBox to table

[JCtheEUC]

I have a ListBox that is populated from a query based on a combo box seletions. The user then chooses multiple selections from the ListBox and is to hit a command button to return those selections to a table.

My problem is..How do I take those mutli-selections and pass them to the table?

Any help is appreciated. Thanks!

 

[ajetrumpet]

You have to use the .selected property of the listbox in combination with the .row index to see if the current row that you are looping on through the listbox is selected or not. If it is, you have to copy it to the table's recordset, or put it into an array, or do whatever you want with it. Check out .selected in the help menu for help on the listbox looping. It is there I think...

This link is good too: http://www.access-programmers.co.uk/forums/showthread.php?t=136282&highlight=multi+select

 

[JCtheEUC]

You have to use the .selected property of the listbox in combination with the .row index to see if the current row that you are looping on through the listbox is selected or not. If it is, you have to copy it to the table's recordset, or put it into an array, or do whatever you want with it. Check out .selected in the help menu for help on the listbox looping. It is there I think...

This link is good too: http://www.access-programmers.co.uk/forums/showthread.php?t=136282&highlight=multi+select

Thanks for the help. Now I have one more problem hopefully you can help with. The data that is being pulled from the ListBox is put into a table but I cannot have duplicates. In this case I can't index the table because of the two fields in the table there can be duplicates just not of the same to two fields.

Example
field 1 field 2
Emp 1 task1
Emp 2 task1
Emp 1 task2
Emp 2 task1 -cannot have

Hopefully that helps you and you can help me.

Thanks!

 

[JCtheEUC]

Thanks for the help. Now I have one more problem hopefully you can help with. The data that is being pulled from the ListBox is put into a table but I cannot have duplicates. In this case I can't index the table because of the two fields in the table there can be duplicates just not of the same to two fields.

Example
field 1 field 2
Emp 1 task1
Emp 2 task1
Emp 1 task2
Emp 2 task1 -cannot have

Hopefully that helps you and you can help me.

Thanks!

PROBLEM SOLVED! Here's how I did it.
http://www.databasejournal.com/features/msaccess/article.php/3077791
I used the example farthest down on the link above and modified it a bit to fit my situation.


This code is to delete duplicate records....

Private Sub Command15_Click()
' Deletes exact duplicates from the specified table.
    ' No user confirmation is required. Use with caution.
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant
    
    Set tdf = DBEngine(0)(0).TableDefs("tlbTempRecordset")
    strSQL = "SELECT * FROM " & "tlbTempRecordset" & " ORDER BY "
    ' Build a sort string to make sure duplicate records are
    ' adjacent. Can't sort on OLE or Memo fields,though.
    For Each fld In tdf.Fields
        If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
            strSQL = strSQL & fld.Name & ", "
        End If
    Next fld
    ' Remove the extra comma and space from the SQL
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <> rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
        rst.Delete
        GoTo SkipBookmark
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop

End Sub

Here is the code to take data from a list box to a table...

Private Sub Command14_Click()
Dim varName As Variant
Dim varItem As Variant
Dim strSQL As String
Dim undSQL As String
Dim CmbValue As String
CmbValue = Me.Combo0.Value
With Me.List9
    For Each varItem In .ItemsSelected
        strSQL = "INSERT INTO tlbTempRecordset (UnderwriterName, ST_CODE) VALUES ('" & .Column(0) & "','" & .ItemData(varItem) & "');"
        DoCmd.RunSQL (strSQL)
        strSQL = ""
    Next varItem
End With
Me.List9.Value = Null
End Sub

Enjoy!