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