Multiselect listbox

[mo]

Situation:

I have two (unbound) listboxes on a form. One of them is populated through
the results of a search fuction (the user enters enters a search criteria or
two in text boxes, hits a 'find' button and the listbox displays the
result(s) of the search if any). This works fine.

What I need to do now is to allow the user to highlight an item in this
first listbox and add it to second listbox via another button, which will
also append the selected record to the table underlying the second listbox
via a liitle bit of code. I set the 'multi select' property of both controls
to 'extended' and came up with the following code which is where I get
stuck:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database

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

For Each varItem In Me.list_main.ItemsSelected
rst.AddNew
rst![HospNum] = Me.list_main!Column(0)
rst![SBC] = Me.list_main!Column(1)
rst![Surname] = Me.list_main!Column(2)
rst.Update
Next varItem

rst.Close
Set rst = Nothing
Set db = Nothing

Me.list_main.Requery
Me.lst_selected.Requery

I get the error message telling me that there is a data type conversion
error on the line 'rst![HospNum] = Me.list_main!Column(0)'. I've also tried
using the following instead, but get a different error.

With rst
.AddNew
.Fields("HospNum") = Me.list_main!Column(0)
etc etc
End With


Can someone point out where I'm going wrong please.

Thanks for any advice.

 

[Rod Scoullar]

I'm not sure you should be using Me.list_main!Column(0). I think the '!' is
incorrect in this context, use a '.' instead.

Rod Scoullar.

 

[mo]

Thanks for that Rod

 

[Jason Grace]

Situation:

I have two (unbound) listboxes on a form. One of them is populated through
the results of a search fuction (the user enters enters a search criteria or
two in text boxes, hits a 'find' button and the listbox displays the
result(s) of the search if any). This works fine.

What I need to do now is to allow the user to highlight an item in this
first listbox and add it to second listbox via another button, which will
also append the selected record to the table underlying the second listbox
via a liitle bit of code. I set the 'multi select' property of both controls
to 'extended' and came up with the following code which is where I get
stuck:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database

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

For Each varItem In Me.list_main.ItemsSelected
rst.AddNew
rst![HospNum] = Me.list_main!Column(0)
rst![SBC] = Me.list_main!Column(1)
rst![Surname] = Me.list_main!Column(2)
rst.Update
Next varItem

rst.Close
Set rst = Nothing
Set db = Nothing

Me.list_main.Requery
Me.lst_selected.Requery

I get the error message telling me that there is a data type conversion
error on the line 'rst![HospNum] = Me.list_main!Column(0)'. I've also tried
using the following instead, but get a different error.

With rst
.AddNew
.Fields("HospNum") = Me.list_main!Column(0)
etc etc
End With


Can someone point out where I'm going wrong please.

Thanks for any advice.

What is the datatype of column(0)? Of HospNum?