Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using a record set in an append query

Discussion in 'Information Technology' started by rg, Jul 28, 2006.

  1. rg

    rg
    Expand Collapse
    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
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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" <rg@discussions.microsoft.com> 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/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
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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" <rg@discussions.microsoft.com> 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/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




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

    rg
    Expand Collapse
    Guest

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

Share This Page