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.
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.
