INSERT INTO tbl values from Unbound ListBox

[pwag]

Good Afternoon Pro's,

I am attempting to append values to a table that are loaded into a listbox. I have found NUMEROUS resources for this topic, however it's all regarding the .ItemsSelected property.

Question: Is there a way to Insert the values of a listbox without actually having the user select the items? Seems like a very simple task but I'm left dumbfounded. The below code works perfectly but I'd like to swap out the .ItemsSelected property with something like ".Items" Hope this makes sense. Thanks again for your time.

Paul

Dim varValue        As Variant
Dim varitem         As Variant


 With Me.List
            For Each varValue In List.ItemsSelected
                 CurrentDb.Execute "INSERT INTO [Team_Proj] ([ID], [Team_Member], ion_ID) Values " & _
                            "('" & Me.Text1 & "', '" & Me.List.Column(0, varValue) & "', " & Me.Text2 & ")"
            Next
        End With

[ADezii]

Assuming you have a 3-Column List Box named lstTest, consisting of the following Fields: [ID], [Last Name], and [First Name], the following Code will display each Row of the List Box in a Comma-Delimited Format:

Dim intCount As Integer
Dim intRowCtr As Integer
Dim lst As ListBox

Set lst = Me![lstTest]

intCount = Me![lstTest].ListCount       'Number of Items

For intRowCtr = 0 To lst.ListCount - 1
  Debug.Print lst.Column(0, intRowCtr) & "," & _
              lst.Column(1, intRowCtr) & "," & _
              lst.Column(2, intRowCtr)
Next

OUTPUT:

2,Cencini,Andrew
1,Freehafer,Nancy
8,Giussani,Laura
9,Hellung-Larsen,Anne
3,Kotas,Jan
6,Neipper,Michael
4,Sergienko,Mariya
5,Thorpe,Steven
7,Zare,Robert

P.S. - The rest you should be able to figure out.

[pwag]

ADezii,

Worked like a charm! Great job.

One last question about this code. I've always been curious about this structure of the FOR NEXT loop.

This Line:

For intRowCtr = 0 To lst.ListCount - 1

I understand this is looping through the index and recording each item accordingly, but how does "0" to "-1" work? Is each iteration subtracting 1 from the index until it reaches:

intCount = Me![lstTest].ListCount

?

Thanks again!
Paul

[ADezii]

The Column Property of the ListBox Control is Zero based and is an Integer that can range from 0 to the setting of the ListCount property minus 1. Each Iteration of the Loop is adding/not subtracting 1. There are 9 Items in the List Box so the actual Loop would be:

For intRowCtr = 0 to 8     '9 Iterations
  'Loop processing Code here
Next

Hope this explains matters well enough.