multi listbox append query = wrong results??? 1

[hovercraft]

Howdy folks. I have a listbox set to multiple select.

I'm trying to append each selection to a temp table. I'm looping through the values of the collection and depending on the number of rows selected, the query fires off that many times...however, each record that is being inserted contains the exact same data as the most currently selected row in the list box.

Can someone please tell me what I'm doing wrong and how I would fix this?

Dim varCase As Variant
Dim strSQL As String
Dim lst As ListBox
Set lst = lst_srchresults
For Each varCase In lst.ItemsSelected
strSQL = "INSERT INTO tmp_tbl_pymts (my_id, my_st, my_cnty, my_case, my_tckt, my_dob, my_totaldue) VALUES ('" & lst.Column(15) & "', '" & lst.Column(4) & "', '" & lst.Column(5) & "', '" & lst.Column(6) & "', '" & lst.Column(7) & "', '" & lst.Column(3) & "', '" & lst.Column(12) & "')"
            DoCmd.RunSQL (strSQL)
Next varCase

Thank you for your assistance.
Hovercraft

 

[PHV]

Replace each occurrence of this:
lst.Column(?)
with this:
lst.Column(?, varCase)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hovercraft]

Thank you PHV! You are the poo!