Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Independence, Kansas
    Posts
    22

    Unanswered: Basing a Recordset on a Recordset

    In VBA coding, Access97, can I reference a recordset in a 2nd recordset? My current code returns a Run Time Error of 3421: “Data Type Conversion Error” when doing so in a fairly simple set of syntax. Ultimately, if I had my way, a 3rd recordset would be referenced as well.

    I have two abbreviated recordsets displayed below to give an idea of what I am talking about but I will mostly appreciate general comments as to if a recordset can be referenced within a 2nd recordset.

    Set rst_All_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & Table_Name & ".Task, " & _
    Table_Name & "." & OG_abbrev & " AS OG_AB, " & _
    Table_Name & ".DFT," & _
    Table_Name & ".Priority," & _
    Table_Name & ".Freq_No," & _
    Table_Name & ".Task_ID " & _
    "FROM " & Table_Name & _
    " ORDER BY " & Table_Name & ".Priority")


    Set rstAvail_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & rst_All_Tasks!Task, _
    rst_All_Tasks!og_ab, _
    rst_All_Tasks!Priority, _
    rst_All_Tasks!Freq_No & _
    " FROM rst_All_Tasks")

    NOTE1: If in the 2nd SQL I use quotes as I normally do, then Access responds that it cannot find the table or query.

    NOTE2: The 1st SQL statement uses variables Table_Name & OG_Abbrev
    Regards, Kaniksu

  2. #2
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: Basing a Recordset on a Recordset

    In access you cannot query recordsets.

    You can use .filter property of the recordset to assign the filtered result to a new recordset.

    Msg me for more info

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •