Form Control SQL Question

[spokane29 via AccessMonster.com]

I have a listbox on my form which I have set to simple. I would like to have
each selection as a separate record in the table. The table that the data is
being stored in is tblResponses. The subform that has the listbox is
frmResponses Subform, which is a subform on the subform frmQuestions which is
a subform on frmQuestionaire. Hopefully I haven't lost you.
I have written the following code so far, that I temporarily have on a button
control, using the OnClick event.

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 = Forms![frmQuestionaire]![frmQuestions]![frmResponses Subform].Form
Set ctl = frm!ListRspns

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

My problem is that I am still learning SQL and VB. I need the data from 4
fields on [frmResponses Subform] to save into the table. The Control names
are ResponseDate, QstnID, AssociateRef, and Rspns. The field names in
tblResponses are named the same.
Any suggestions on how to do that? I looked at the DAO option, and that is
even more confusing to me.

 

[Guest]

Surely
db.Execute "INSERT INTO tblResponses(Rspns,ResponseDate, QstnID,
AssociateRef) VALUES ('"" & ctl.

itemdata(varItem) & "","" & me.ResponseDate & "","" & me.QstnID & "","" & me.AssociateRef &""");" (If I've got the quotes in the right place!)

would be what you need. Of course it depends if I have understood your
situation correctly. Didn't get the bit about DAO solution, the above looks
like DAO code to me. Looks to me as though you have got to grips with VBA
pretty well, however I suspect that your table structure and form structure
is more complex than it needs to be (based on the sub form within a subform
within a subform scenario you describe) but it is hard to advise on that
without more detailed info.

Good luck

Alan

I have a listbox on my form which I have set to simple. I would like to have
each selection as a separate record in the table. The table that the data is
being stored in is tblResponses. The subform that has the listbox is
frmResponses Subform, which is a subform on the subform frmQuestions which is
a subform on frmQuestionaire. Hopefully I haven't lost you.
I have written the following code so far, that I temporarily have on a button
control, using the OnClick event.

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 = Forms![frmQuestionaire]![frmQuestions]![frmResponses Subform].Form
Set ctl = frm!ListRspns

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

My problem is that I am still learning SQL and VB. I need the data from 4
fields on [frmResponses Subform] to save into the table. The Control names
are ResponseDate, QstnID, AssociateRef, and Rspns. The field names in
tblResponses are named the same.
Any suggestions on how to do that? I looked at the DAO option, and that is
even more confusing to me.