List box question

[spokane29 via AccessMonster.com]

I have a list box which I have set to Multiselect=Simple. I want to store
each item selected to its own record. The form I am using is a subform
within a subform. The form name is [frmResponses Subform]. In the form
structure, the main form is frmQuestionaire, the first subform is
tblQuestions, and then tblResponses Subform is a subform within tblQuestions.
When I attempt the code below, I get a Run-time error '2465': Microsoft
Access can't fin the field 'frmQuestionaire' referred to in your expression.
If I change it to jsut the name of the subform [frmResponses Subform] I get
the same error. It errors at the Set frm line.
Any suggestions as to what may be wrong?

Dim db As Database: Set db = CurrentDb
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim SQLstr As String
Set frm = Form![frmQuestionaire]![tblQuestions]![tblResponses Subform]

Set ctl = frm!ListRspns

For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO tblResponses(Rspns) VALUES ('"" & ctl.
itemdata(varItem) & ""');"
Next varItem

 

[Douglas J Steele]

Subforms aren't actually put in the Forms collection: only the parent form
is.

It's not necessary to declare frm, though. You should be able to use:

Set ctl = Form![frmQuestionaire]![tblQuestions].Form![tblResponses
Subform].Form!ListRspns

Note that [tblQuestions] and [tblResponses Subform] refer to the name of the
subform container control on the parent form. That may or may not be the
same as the form that's being used as a subform. If you created the subform
by dragging the form onto the parent form, they should be the same name.
However, if you selected the Subform control from the toolbox and added it
to your form, the container will be named something like "Child0"

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
more information about referring to forms and subforms.