Use String Query As Recordset for Second String Query in VBA

Posted on 2014-01-02
Last Modified: 2014-01-02
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
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.
Question by:gwflyer
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39751516
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.

Author Closing Comment

ID: 39753184
Thank you very much for helping with this.  I did take your advice and created a stored query.  All is working fine now.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question