Saving Listbox Items

[gdspeare]

Hello all -

I am trying to save the records in a listbox to an access table and
am having trouble. I am close, but can't seem to place everthing in
the right place no matter how many times i try. The code below is
wrting the correct number of records to the table, but the value of
the listbox item is not being written. I'm sure it is something
stupid. Thanks,

Private Sub cmdSave_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\speared\My
Documents\db1.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Test", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table

For i = 0 To ListBox2.ListCount - 1
With rs
.AddNew 'create a new record
.Fields("Company") = ListBox2.Value
.Update 'stores the new record
End With
Next i
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[MyBoo]

Have you tried changing

.Fields("Company") = ListBox2.Value
to
.Fields("Company") = Me!ListBox2.Value

 

[VicRauch]

I see that you are doing a loop for the number of items in the list, but I don't see any code that says "Select the next item in the list." Or, anything that would select each item, one at a time during the For/Next loop. The ListBox property, "ListIndex" is what is used to select individual items in the list with VBA code. By setting " ListBox2.ListIndex = i " within your loop, then you can move the ListBox2.Value into your company name field.
HTH,