Using a record set in an append query

#1 (**permalink**) 28-Jul-2006, 08:31 AM

I read an excelent article on append queries
(http://office.microsoft.com/en-au/as...860631033.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12775-using-a-record-set-append-query.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
the user has filtered to make the new records?
-rg

#2 (**permalink**) 28-Jul-2006, 08:32 AM

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" wrote in message
news:CBC35725-07F3-4FC2-9D81-1053D5BDBAC2@microsoft.com...
>I read an excelent article on append queries
> (http://office.microsoft.com/en-au/as...860631033.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
>
> 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

#3 (**permalink**) 28-Jul-2006, 08:32 AM

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 & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
"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" wrote in message
news:CBC35725-07F3-4FC2-9D81-1053D5BDBAC2@microsoft.com...
>I read an excelent article on append queries
> (http://office.microsoft.com/en-au/as...860631033.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

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4182 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

#4 (**permalink**) 28-Jul-2006, 08:32 AM

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

"Pieter Wijnen" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
> Set QDef = Db.CreateQueryDef(VBA.vbNullString)
> QDef.SQL="PARAMETERS p1 Long, p2 long;"; & VBA.vbCrlf & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12775
> "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
>
>