Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: Query Recordsets, cloning, etc

    MS Access 2002 (XP)

    I have a form for tracking a request (Form1) and on it a subform (Subform1) listing all related attachments. NOTE: the subform's query/sql joins two tables to get the results. I want to grab the Title and doc ID for any record showing in the subform and put the text as a string in an unbound field on another form (Form2), which helps the user build a memo report. For multiple attachs, the memo's ATTACH field (string from Attachs variable) would look something like: "ID #123, Doc 1; ID #345, Doc 2", etc.

    I saved the subform's code as a sep. query ("Query2", ref'd below) then tried to pull from it as a recordset using the following code but it won't run for a query. Only works, I guess, for a recordset from a table. Ideally, I'd be able to pull from the underlying form's subform, instead of having this sep. query. or having to create a temp. table then run through it's recordset.

    So basically, I want to loop through an open query (subform) that pulls only the relevant records and compose a string from a few fields of each returned record in that query.

    Any ideas how to get the desired results without a make table query that's temporary?

    BTW, I'm not a VBA expert so I'd appreciate as much detail as possible in any replies (i.e. please don't assume I'll know or be able to expand quick/general code).

    Thanks so much in advance to anyone kind and patient enough to help.
    Dim w As Workspace
    Dim db As Database
    Dim t As Recordset
    Dim X As Integer
    Dim Attachs, DocInfo As String

    Set w = DBEngine.Workspaces(0)
    Set db = w.Databases(0)
    Set t = db!Query2.OpenRecordset()

    Do Until t.EOF
    DocInfo = t![TN_Sub] & t![TN_folder] & t![TN_doc] & t![Title]
    Attachs = Attachs & "; " & DocInfo

    here's the subform's query, in case it's needed

    SELECT tbl_Findings_Inventory.Findings_ID, tbl_Inventory.Title, tbl_Inventory.TN_subject, tbl_Inventory.TN_folder, tbl_Inventory.TN_doc
    FROM tbl_Inventory INNER JOIN tbl_Findings_Inventory ON tbl_Inventory.Inventory_ID=tbl_Findings_Inventory. Inventory_ID;

  2. #2
    Join Date
    Jul 2004
    Southampton, UK
    Try replacing

    Set t = db!Query2.OpenRecordset()
    Set t = db.OpenRecordset("Query2")

    I assume Query2 works on it's own.


Posting Permissions

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