Multiselect problem

[mo]

I'm using the code below in the click event of a button control to append
data from one table to another. The code works well if just one item is
selected from the first list box (list_main) when the event is fired.
However, when more than item is selected problems appear.

The code successfully appends the SBC for each item selected, but for each
of the other fields the same value is appended. The data in the second
listbox on my form might then look like this:

SBC HospNum Surname Forename DoB
105704081 5014382 SMITH EMMA 09/12/1983
105705482 5014382 SMITH EMMA 09/12/1983
105705859 5014382 SMITH EMMA 09/12/1983
105706006 5014382 SMITH EMMA 09/12/1983

when it should look like this:

SBC HospNum Surname Forename DoB
105704081 5014382 SMITH EMMA 09/12/1983
105705859 013019 SMITH ANN 16/08/1971
105705482 A378696S SMITH JANE 21/05/1980
105706006 A488065 SMITH ANGELA 24/12/1962

Does anyone know what I'm doing wrong?

Thanks for any help.

Code:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSBC As String
Dim strCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT HospNum, SBC, Surname, Forename, DoB,
FreeT4, TSH FROM tbl_Lists", dbOpenDynaset)

strSBC = Me.list_main.Column(0)

If strSBC = "" Then

MsgBox ("No item selected!"), vbOKOnly, "Item not selected"

Else

strCriteria = "((tbl_lists.[sbc] = '" & strSBC & "'))"

For Each varItem In Me.list_main.ItemsSelected
With rst
.AddNew
.Fields("SBC") = Me.list_main.ItemData(varItem)
.Fields("HospNum") = Me.list_main.Column(1)
.Fields("Surname") = Me.list_main.Column(2)
.Fields("Forename") = Me.list_main.Column(3)
.Fields("DoB") = Me.list_main.Column(4)
.Fields("TSH") = Me.list_main.Column(5)
.Fields("FreeT4") = Me.list_main.Column(6)
.Update
End With
Next varItem

rst.Close

Set rst = Nothing
Set db = Nothing

Me.list_main.Requery
Me.lst_selected.Requery

End If

 

[JohnFol]

It's the variable called varItem that changes for each entry in the
ItemsSelected. That is why the following line works

Me.list_main.ItemData(varItem)

Every other line just refers directly to the control and not the changing
variable VarItem

 

[JohnFol]

PS the following does not work
Dim varItem As Variant
For Each varItem In me.List_main.ItemsSelected
Debug.Print varItem, me.List_main.Column(2, varItem)
Next varItem

But this does . . ..

Dim varItem As Variant
Dim y As Long

For Each varItem In me.List_main.ItemsSelected
y = varItem
Debug.Print varItem, me.List_main.Column(2, y)
Next varItem

It's the variable called varItem that changes for each entry in the
ItemsSelected. That is why the following line works

Me.list_main.ItemData(varItem)

Every other line just refers directly to the control and not the changing
variable VarItem

I'm using the code below in the click event of a button control to append
data from one table to another. The code works well if just one item is
selected from the first list box (list_main) when the event is fired.
However, when more than item is selected problems appear.

The code successfully appends the SBC for each item selected, but for each
of the other fields the same value is appended. The data in the second
listbox on my form might then look like this:

SBC HospNum Surname Forename DoB
105704081 5014382 SMITH EMMA 09/12/1983
105705482 5014382 SMITH EMMA 09/12/1983
105705859 5014382 SMITH EMMA 09/12/1983
105706006 5014382 SMITH EMMA 09/12/1983

when it should look like this:

SBC HospNum Surname Forename DoB
105704081 5014382 SMITH EMMA 09/12/1983
105705859 013019 SMITH ANN 16/08/1971
105705482 A378696S SMITH JANE 21/05/1980
105706006 A488065 SMITH ANGELA 24/12/1962

Does anyone know what I'm doing wrong?

Thanks for any help.

Code:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSBC As String
Dim strCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT HospNum, SBC, Surname, Forename, DoB,
FreeT4, TSH FROM tbl_Lists", dbOpenDynaset)

strSBC = Me.list_main.Column(0)

If strSBC = "" Then

MsgBox ("No item selected!"), vbOKOnly, "Item not selected"

Else

strCriteria = "((tbl_lists.[sbc] = '" & strSBC & "'))"

For Each varItem In Me.list_main.ItemsSelected
With rst
.AddNew
.Fields("SBC") = Me.list_main.ItemData(varItem)
.Fields("HospNum") = Me.list_main.Column(1)
.Fields("Surname") = Me.list_main.Column(2)
.Fields("Forename") = Me.list_main.Column(3)
.Fields("DoB") = Me.list_main.Column(4)
.Fields("TSH") = Me.list_main.Column(5)
.Fields("FreeT4") = Me.list_main.Column(6)
.Update
End With
Next varItem

rst.Close

Set rst = Nothing
Set db = Nothing

Me.list_main.Requery
Me.lst_selected.Requery

End If

 

[mo]

Thanks very much John. I'll try it out and see.