MS Access Filtering Query Recordset

[Mick99]

Hi All, I am having issues trying to filter a recordset. I have the first recordset that gives me a result for “RScoloumdetail(1)” and that value I am trying to use to filter the second query to only return results matching the result of “RScoloumdetail(1)”

I have the “RScoloumdetail(1)” returning the correct result of “MT” and I am trying to use this to filter a query called [Qry Email Attachments] and where the field value [RA] is equal to RScoloumdetail(1)")

This is the code and the error I get
Set coldetail = dbsCheck.OpenRecordset("Select [Qry Email Attachments] where [RA]= RScoloumdetail(1)")





Any help would be greatly appreciated.

[June7]

RScoloumdetail(1) is a field in [Qry Email Attachments]? If it isn't, then this won't work because cannot reference field in a table that is not included in the query.

And why use WHERE? Why not do a JOIN between table and query linking on RA and RScoloumdetail(1)?

Advise not to use spaces or punctuation/special characters (underscore only exception) in names. If used, enclose in [ ].

Post the full SQL statements.

[Mick99]

RScoloumdetail(1) is from the first query "[Qry Emails Tracking]" which i loop through to get a single value and simply need to use that to filter the second query, if that makes sense.

Set dbsCheck = CurrentDb
Set rstRecords = dbsCheck.OpenRecordset("Select * From [Qry Emails Tracking]")
If rstRecords.RecordCount = 0 Then
MsgBox "Sorry, There is no Data for this selection.", vbInformation, "No Data"
Exit Sub
Else
FileName = "Email Correspondence " & " " & RScoloumdetail(1)
End If

Dim DocumentName As String
Dim AttachRecords As DAO.Recordset
Dim Attachmentcoloumdetail As Variant
Dim rst As DAO.Recordset
Dim RA As String
Dim ParentRole As String

ParentRole = RScoloumdetail(1)
Set Attachmentcoloumdetail = dbsCheck.OpenRecordset("[Qry Email Attachments] where [Qry Email Attachments]![RA] = ParentRole")

[Mick99]

Set Attachmentcoloumdetail = dbsCheck.OpenRecordset("[Qry Email Attachments] where [Qry Email Attachments]![RA] = ParentRole")

[June7]

Variables must be concatenated.

("[Qry Email Attachments] where [Qry Email Attachments]![RA] = '" & ParentRole & "'")

Don't see any looping structure.

Is RScoloumdetail(1) a field in rstRecords? If you want to reference a field of recordset: rstRecords![RScoloumdetail(1)]

And setting a Variant variable to a recordset is not something I've ever seen.

[Mick99]

Hi June7, this has been solved with the code below. I greatly appreciate your help with this issue.


Set Attachmentcoloumdetail = dbsCheck.OpenRecordset("Select * FROM [Qry Email Attachments] WHERE [RA] = '" & RScoloumdetail(1) & "'")