saving listbox choices to a table

P

prairie smoke

I am using the ItemsSelected collection to move the selections in a listbox
to a one-column table. In looking at examples in books, etc., this code
looks close, but no "prize." The code produces error 3219: Invalid
Operation. I don't quite get how to move several choices to the table. The
code is below. Thanks for any suggestions you can offer.

Prairie Smoke

Dim db As Database
Dim frm As Form, ctl As Control, tbl As TableDef
Dim varItm As Variant, fld As Field

Set db = CurrentDb()
Set frm = Forms!frmQueryGenerator
Set fld = db!tblFunction_Code!gl_code
Set ctl = frm!lstFunctionNumber
For Each varItm In ctl.ItemsSelected
fld = ctl.ItemsSelected(varItm)
Next varItm
End Sub
 
K

Klatuu

If you are writing to a table, you have to create a recordset.

Dim db As Database
Dim frm As Form, ctl As Control, tbl As TableDef
Dim varItm As Variant
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db(OpenRecordset, "tblFunction_Code", dbOpenDnaset)
Set frm = Forms!frmQueryGenerator
Set ctl = frm!lstFunctionNumber

For Each varItm In ctl.ItemsSelected
With rst
.AddNew
!gl_code = ctl.ItemsSelected(varItm)
.Update
End With
Next varItm

rst.Close

Set db = Nothing
Set rst = Nothing
Set frm = Nothing
Set ctl = Nothing
End Sub
 
P

prairie smoke

Klatuu:

This is great, thanks. Now I'm remembering details I've forgotten (haven't
done this in awhile). However, I'm getting a "variable undefined" on
recordset. Also, I put a "y" in Dynaset, but still get error.

prairie smoke
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top