Use String Query As Recordset for Second String Query in VBA

I am trying to building a query in VBA using another query in the same VBA.  Here is my code:

Dim dbs As Database
Dim rs2 As Recordset
Dim str2 As String
Dim str3 As String

Set dbs = CurrentDb()

str = [Forms]![frmClinic_Main]![ClinicID]

Set rs2 = CurrentDb.OpenRecordset("SELECT * from tblEnrollment_byAge WHERE EnrSite =" & Chr(34) & str & Chr(34) & ";", dbOpenSnapshot)

str3 = "SELECT rs2.AgeCombo, " & _
        "Sum(rs2.TotalEnrolled) AS TotalEnrolled FROM qdf INNER JOIN " & _
        "tblAgeGroupRanking ON rs2.AgeCombo = tblAgeGroupRanking.AgeCode GROUP BY " & _
        "rs2.AgeCombo, tblAgeGroupRanking.Rank ORDER BY tblAgeGroupRanking.Rank;"

With dbs
qdf1 = .CreateQueryDef("TempTable", str3)
DoCmd.OpenQuery "TempTable"
.QueryDefs.Delete "TempTable"
End With
dbs.Close
Set qdf1 = Nothing

Is this possible to do?  Nothing happens when I click the command button.  I'm using the QueryDef method to view my results.    Any help you can provide is greatly appreciated.
Thank you very much.
gwflyerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Perhaps this query will work:

SELECT EBA.AgeCombo, Sum(EBA.TotalEnrolled) AS TotalEnrolled FROM tblEnrollment_byAge AS EBA
INNER JOIN tblAgeGroupRanking AS AGR EBA.AgeCombo = AGR.AgeCode
HAVING EBA.EnrSite='" & str & "' GROUP BY EBA.AgeCombo, AGR.Rank ORDER BY ARG.Rank

If it does, then just create a stored query with that as the SQL, and then use DoCmd.OpenQuery to open that query. Of course, if you do that, you'd have to change the way you refer to EnrSite, something like:

SELECT EBA.AgeCombo, Sum(EBA.TotalEnrolled) AS TotalEnrolled FROM tblEnrollment_byAge AS EBA
INNER JOIN tblAgeGroupRanking AS AGR EBA.AgeCombo = AGR.AgeCode
HAVING EBA.EnrSite=[Forms]![YourForm]!YourControl GROUP BY EBA.AgeCombo, AGR.Rank ORDER BY ARG.Rank

Better yet, create a Form based on that query and let the user view the data through the form. Queries are for data manipulation, not for direct user viewing.
0
 
gwflyerAuthor Commented:
Thank you very much for helping with this.  I did take your advice and created a stored query.  All is working fine now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.