LIST BOX help needed

[Gazer44]

I have set up a List Box to allow multiple selections (set at simple) but need these mult. selections to be saved onto a table, how do i do this.

any info appreciated, cheers

 

[930driver]

A list box has a .ItemsSelected property which is a collection of the selected items. Using VBA you can loop through the collection and have your way with them.

Sub GetData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!FormName
Set ctl = frm!ListBox
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
'do your dirty work here
Next varItm
End Sub
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[danvlas]

Dim itm
DoCmd.SetWarnings False
For Each itm In YourListBox.ItemsSelected
DoCmd.RunSQL "Insert Into YourTableName (FieldName) Values ('" & YourListBox.ItemData(itm) & "')"
DoCmd.SetWarnings True
Next

I'd use a recordset, but it depends on your Access version(DAO or ADO)

Good luck,


Daniel Vlas
Systems Consultant

 

[Gazer44]

thanks for the help but i'm not too well up on vb programming yet. can you help any further with maybe the full code as both reply's look to me like they contain only part of the answer. if not can you explain, cheers

 

[930driver]

Here's the basics in DAO code. You will have to modify field and table names to suit.

No error handling in this, so you should add your own.


Sub GetData()
Dim ctl As Control
Dim varItm As Variant

'set up a recordset
Dim db As Database
Dim rs As Recordset
Dim sql As String
sql = "tblMyTable" 'sql is a table or query name, or a sequel string
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

ctl = Me.ListBox
For Each varItm In ctl.ItemsSelected
'Debug.Print ctl.ItemData(varItm)
'do your dirty work here

'new record
rs.AddNew
rs.FieldName = ctl.ItemData
'save it
rs.Update

Next varItm

'clean up the recordset stuff
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"