Insert Into using RecordSet

[Guest]

I want to use the filtered RecordSet from a sub-form to create new records in
a table. I have a command button on the form, but I don't know how to use the
record set in the insert into statement.
-rg

 

[Dirk Goldgar]

I want to use the filtered RecordSet from a sub-form to create new
records in a table. I have a command button on the form, but I don't
know how to use the record set in the insert into statement.

You can't. You can loop through the recordset to build and execute a
separate INSERT statement for each record, or you can open another
recordset on your target table, then loop through the filtered recordset
and use the target recordset's .AddNew and .Update methods to add a new
record to the second recordset for each record in the first one. But
you can't write a query that actually refers to the recordset object
itself.

 

[Douglas J. Steele]

You need to put the values outside of the quotes:

strSQL = "Insert Into TempStudent_Task(StudentID, TaskID)" & _
"VALUES(" & me!sfrmStudent!StudentID & ", " & me!TaskID & ")"

This assumes that StudentID and TaskID are both numeric fields.

If they were Text fields, you'd need quotes around the values:

strSQL = "Insert Into TempStudent_Task(StudentID, TaskID)" & _
"VALUES('" & me!sfrmStudent!StudentID & "', '" & me!TaskID & "')"

Exagerated for clarity, that's

strSQL = "Insert Into TempStudent_Task(StudentID, TaskID)" & _
"VALUES( ' " & me!sfrmStudent!StudentID & " ' , ' " & me!TaskID & " ' )"

 

[Douglas J. Steele]

Sorry, I didn't notice before, but the reference to the control on the
subform is wrong.

To refer to a control on a subform from the parent form, you use:

Me!NameOfSubformControlOnParent.Form!NameOfControlOnSubform

Depending on how you added the subform to the parent, the name of the
subform control may not be the same as the name of the form being used as a
subform. However, try Me!sfrmStudent.Form!StudentID first.

However, now that I look at your entire code (all I was looking at before
was the SQL), what are you trying to do?

You're looping through a recordset, but you're referring to controls on a
form. That means you're going to get the exact same SQL every time.

If you're trying to pick up values from the recordset, you need to refer to
the recordset.

If you're trying to pick up values from the subform's recordset, you'll need
to refer to it correctly:

rst = Me!sfrmStudent.Form.RecordsetClone

(assuming, as above, that the name of the subform control is sfrmStudent)

and you probably want

strSQL = "Insert Into TempStudent_Task(StudentID, TaskID)" & _
"VALUES('" & rst!StudentID & "', '" & Me!TaskID & "')"

 

[Douglas J. Steele]

As I suggested elsewhere in this thread, try

rst = Me!sfrmStudent.Form.RecordsetClone

 

[Guest]

I'm getting the error message: Invalid use of property when I run the first
part of the code -
Dim rst As Recordset
Dim strSQL As String

rst = Me!sfrmStudent.Form.RecordsetClone
I checked, and the name of the subform is correct. I copy and pasted from
the properties box just to make sure. Should it say subform instead of form,
or is it something else?
Thanks for you help.
-rg

 

[Dirk Goldgar]

As I suggested elsewhere in this thread, try

rst = Me!sfrmStudent.Form.RecordsetClone

The keyword "Set" is missing:

Set rst = Me!sfrmStudent.Form.RecordsetClone

 

[Guest]

Thanks, unfortunately, I misssed your reply the first time and just figured
out from reviewing the VBA Language Reference that I was missing the keyword
"Set"
-rg