take values from listbox on form and INSERT INTO table

[Guest]

I have a piece of code that I am trying to use to insert values from a
listbox on a form into a table, but I keep getting an error message:
"Microsoft Access can't find the field 'frmROEsList' referred to in your
expression."

It stops right at the line:
Set frm = form!frmROEsList

I thought this line is supposed to define the form, frmROEsList, but Access
is thinking there needs to be a field. Why/what field? I am trying to get
the data from the listbox.

Here is my code as is when a user clicks a line in the listbox:
Private Sub lstOracleLinesForROEs_Click()
Dim frm As Access.Form
Dim ctl As Control
Dim db As DAO.Database
Dim varItem As Variant

Set db = CurrentDb
Set frm = Form!frmROEsList
Set ctl = frm!lstOracleLinesForROEs

For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO tblTest(Oracle#)" & " VALUES (""" &
ctl.ItemData(varItem) & """)"
Next varItem
End Sub

 

[Guest]

After form! you need the name of the form, then the control name

-Dorian

 

[Guest]

Oops, all I needed was an "s" on the end of form!
DUH!
forms!....works

But now says I have a syntax error in my INSERT INTO statement!!!

 

[Guest]

Thanks, I have that. It was actually a misspelling on my part.
Instead of:
Set frm = form!frmROEsList

It should have been:
Set frm - forms!frm ROEsList

I was missing the "s" on forms!

Now, I am getting a syntax error on my INSERT INTO statement
Do you see anything glaringly wrong???

 

[Guest]

I finally got the syntax of the INSERT INTO statement to work after fixing
the issue with the form! vs. forms! reference, but it seems to be inserting a
random value into my table supposedly from my listbox. At least my intention
is to use the data on the line clicked in the listbox. I can't find the
value it is inserting into my table anywhere in the original source of the
info that even populates the list box.