Saving Listbox Items

gdspeare

Board Regular
Joined
Oct 8, 2002
Messages
198
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Have you tried changing

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

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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,
 

Forum statistics

Threads
1,136,272
Messages
5,674,753
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top