Listbox and append to table

[hockeyfan21]

I am trying to get the below to work to loop through my listbox (List2) and append the new records to my table.
It will only append the 1st record added to the listbox but is appending it multiple times based on how many items are selected. For example I have the following items in the list box ready to append

item1: 1 3 15
item2: 1 3 13
item3: 1 3 9

It is append item1 3 times instead of item1,item2 and item3. I am at a loss on this one, appreciate any help you could give.

Thanks!

Toni

Private Sub cmdAddAccounts_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim lngRow As Long

On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("AccountsToProjects", dbOpenDynaset, dbAppendOnly)
Set ctl = Me.List2
If ctl.MultiSelect Then
For lngRow = 0 To ctl.ListCount - 1
ctl.Selected(lngRow) = True
Next

End If

'add selected Accounts to table

For Each varItem In ctl.ItemsSelected
rs.AddNew

rs!ProjectID = Me.ID
rs!GeoID = ctl.Column(0, varItem)
rs!AccountId = ctl.Column(1)

rs.Update
Next varItem

Me.List2.RowSource = ""
MsgBox "You have successfully added the new items", vbOKOnly

ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

 

[Isskint]

hockeyfan,

i think the problem is with ... rs!AccountId = ctl.Column(1). You need to include the row argument otherwise it will take the first selected row. Try rs!AccountId = ctl.Column(1, varItem)

Oh and just out of curiosity, why do you use code to select all the items in the list box?

If ctl.MultiSelect Then
For lngRow = 0 To ctl.ListCount - 1
ctl.Selected(lngRow) = True
Next

 

[hockeyfan21]

Isskint, that was it, thank you! Working as it should. Regarding your question, I didn't want the user to have to highlight all of the items in list2 in order to add to the table so this just does it for them.

Thanks again!

Toni