Using a record set in an append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I read an excelent article on append queries
(http://office.microsoft.com/en-au/assistance/HA011860631033.aspx) and I want
to use an append query to create records from user choices on a form. The
form has 2 subforms with information from 2 different tables. I want to
create new records in a third table from this form. One subform allows the
user to filter students so that they have the group they want to assign tasks
to. The other subform has the tasks that are to be assigned. The table
containing the tasks is a temporary table that holds just the tasks the user
has picked.

I want to create a record that has the student's ID from the first table and
the task from the second table. I want one record for each student/task
combination. There may be any number of students and up to 3 tasks.

How can I make the append query pick the students that are in the recordset
the user has filtered to make the new records?
-rg
 
Pretty simple

Sub Apply_click()
Dim RsSF1 AS DAO.Recordset
Dim RsFS2 As DAO.Recordset
Dim Db AS DAO.Database
Dim QDef AS DAO.QueryDef

Set Db = Access.Currentdb
Set QDef = Db.CreateQueryDef(VBA.vbNullString)
QDef.SQL="PARAMETERS p1 Long, p2 long;"; & VBA.vbCrlf & _
"INSERT INTO TABLE3 (SF1, SF2) VALUES(p1, p2)

Set RsFs1 = Me.SF1.Form.Recordsetclone 'substitute SFx
Set RsFs2 = Me.SF2.Form.Recordsetclone
RsFs1.MoveFirst

While Not RsFs1.EOF
Qdef.Parameters("p1").value = RsF1.Fields(0).Value ' Assuming First
field of the recordsource holds the value
RsFs2.Movefirst
While Not Rsf2.EOF
Qdef.Parameters("p2").value = RsF2.Fields(0).Value ' Assuming First
field of the recordsource holds the value
QDef.Execute DAO.dbSeeChanges
Wend
Wend
' object cleanup recommended

Hope this gets you on the track

Pieter
 
Pretty simple

Sub Apply_click()
Dim RsSF1 AS DAO.Recordset
Dim RsFS2 As DAO.Recordset
Dim Db AS DAO.Database
Dim QDef AS DAO.QueryDef

Set Db = Access.Currentdb
Set QDef = Db.CreateQueryDef(VBA.vbNullString)
QDef.SQL="PARAMETERS p1 Long, p2 long;"; & VBA.vbCrlf & _
"INSERT INTO TABLE3 (SF1, SF2) VALUES(p1, p2)

Set RsFs1 = Me.SF1.Form.Recordsetclone 'substitute SFx
Set RsFs2 = Me.SF2.Form.Recordsetclone
RsFs1.MoveFirst

While Not RsFs1.EOF
Qdef.Parameters("p1").value = RsF1.Fields(0).Value ' Assuming First
field of the recordsource holds the value
RsFs2.Movefirst
While Not Rsf2.EOF
Qdef.Parameters("p2").value = RsF2.Fields(0).Value ' Assuming First
field of the recordsource holds the value
QDef.Execute DAO.dbSeeChanges
Wend
Wend
' object cleanup recommended

Hope this gets you on the track

Pieter



rg said:
I read an excelent article on append queries
(http://office.microsoft.com/en-au/assistance/HA011860631033.aspx) and I
want
to use an append query to create records from user choices on a form. The
form has 2 subforms with information from 2 different tables. I want to
create new records in a third table from this form. One subform allows
the
user to filter students so that they have the group they want to assign
tasks
to. The other subform has the tasks that are to be assigned. The table
containing the tasks is a temporary table that holds just the tasks the
user
has picked.

I want to create a record that has the student's ID from the first table
and
the task from the second table. I want one record for each student/task
combination. There may be any number of students and up to 3 tasks.

How can I make the append query pick the students that are in the
recordset
the user has filtered to make the new records?
-rg
Click to expand...



--
 
Pieter,
I'm trying to understand what you've written, and I think I get most of it.
However, I don't see where this steps through the records in the recordset.
Also, is it inserting the records one at a time, or building the append query
to insert all of them at the same time. Sorry, I'm really new to Access and
I"m trying to get up to speeed. Thanks for you help.
-rg
 
Back
Top