Query another Query to get a line total.

[mhw1129]

I'm having trouble finding a solution to querying a query using VBA. When I run this code it gives me an error that access cannot find the table or query 'sql'. It gives the same error when I try SQLQuery instead. Do I have to declare something differently to reference a query like this later on? I would really appreciate any help anyone can give me.

'Open Client's Cumulative Savings file, update info for selected Account
Private Sub btnCumulativeSavings2_Click()
Dim csFileName, clientDir, ClientName, acctName As String
Dim ClientID, acctID As Integer
Dim sql, sqltotal As DAO.Recordset
Dim xlApp As Excel.Application
Dim csBook As Excel.Workbook
Dim vsheet As Excel.Worksheet
Dim SQLQuery, sqlquerytotal As String
Dim clientRS As Recordset
Dim sqlattriblistid, sqlattribquant, sqlattribcost, sqlbillperid, sqlmobilecost, sqlaccountname, sqlshortname, sqlaccountparent, mobilerateid As DAO.Field
Dim csdb As DAO.Database
Set csdb = CurrentDb


SQLQuery = "SELECT tblMobileDetail.MobileID, tblAttributeDetail.AttributeListID, tblAttributeDetail.AttributeQuantity, tblAttributeDetail.AttributeCost, tblMobileDetail.BillingPeriodID, tblMobileDetail.MobileRatePlanCost, tblAccounts.AccountName, tblOrg.ShortName, tblAccounts.AccountParentID, tblMobileDetail.MobileRatePlanID FROM tblOrg RIGHT JOIN (tblAccounts INNER JOIN (tblMobileNumber INNER JOIN (tblMobileDetail INNER JOIN tblAttributeDetail ON tblMobileDetail.MobileDetailID = tblAttributeDetail.MobileDetailID) ON tblMobileNumber.MobileNumberID = tblMobileDetail.MobileID) ON tblAccounts.AccountID = tblMobileNumber.AccountID) ON tblOrg.OrgID = tblAccounts.OwnerOrgID WHERE (((tblAttributeDetail.AttributeListID)=268) AND ((tblMobileDetail.BillingPeriodID)=" & Me.Combo4 & ") AND ((tblAccounts.OwnerOrgID)=" & Me.cboClient & ") AND ((tblAccounts.AccountName)='" & Me.lstAccount.Value & "'));"
Set sql = csdb.OpenRecordset(SQLQuery, dbOpenDynaset)
sql.MoveLast
sql.MoveFirst
ClientID = cboClient
acctID = lstAcct

sqlquerytotal = "select * from sql;"
Set sqltotal = csdb.OpenRecordset(sqlquerytotal, dbOpenDynaset)




Set clientRS = csdb.OpenRecordset("SELECT * FROM tblOrg WHERE OrgID = " & ClientID)
'SQLQuery = CurrentDb.QueryDefs("CumulativeSavings").Sql

 ' If clientRS.RecordCount > 0 Then
        clientRS.MoveLast
        clientRS.MoveFirst
        ClientName = clientRS!shortname
        clientDir = getValueForKey("Dir.Root") & getValueForKey("Dir.Accounts.Active")
        'csFileName = clientDir & ClientName & "\" & ClientName & ".CumulativeSavings.xls"
        csFileName = "C:\CumulativeSavings.xlsx"
        Set xlApp = New Excel.Application
        xlApp.Visible = True
        Set csBook = xlApp.Workbooks.Open(csFileName, True, False)
        
        
        'vsheet.Range("V3").Value = ClientName & " Work Optimizer"
'      GoTo Exit_Sub
  '  Else
   '     Exit Sub
   ' End If
        Set sqlattriblistid = sql("AttributeListID")
        Set sqlattribquant = sql("AttributeQuantity")
        Set sqlattribcost = sql("AttributeCost")
        Set sqlbillperid = sql("BillingPeriodID")
        Set sqlmobilecost = sql("MobileRatePlanCOst")
        Set sqlaccountname = sql("AccountName")
        Set sqlshortname = sql("ShortName")
        Set sqlaccountpart = sql("AccountParentID")
        Set sqlmobilerateid = sql("MobileRatePlanID")
        If sqlaccountpart = 2 Then
        Set vsheet = csBook.Sheets("VZW")
        Else
        If sqlaccountpart = 4 Then
        Set vsheet = csBook.Sheets("SPT")
        Else
        If sqlaccountpart = 3 Then
        Set vsheet = csBook.Sheets("ATT")
        Else
        If sqlaccountpart = 6 Then
        Set vsheet = csBook.Sheets("TMO")
        End If
        End If
        End If
        End If
        
        
        n = 0
        sql.MoveFirst
        
        Do Until sql.EOF
        If n < sql.RecordCount Then
        vsheet.Range("B8").Offset(n, 0).Value = sqlattriblistid.Value
        vsheet.Range("C8").Offset(n, 0).Value = sqlattribquant.Value
        vsheet.Range("D8").Offset(n, 0).Value = sqlattribcost.Value
        vsheet.Range("E8").Offset(n, 0).Value = sqlbillperid.Value
        vsheet.Range("F8").Offset(n, 0).Value = sqlmobilecost.Value
        vsheet.Range("G8").Offset(n, 0).Value = sqlshortname.Value
        vsheet.Range("H8").Offset(n, 0).Value = sqlaccountpart.Value
        vsheet.Range("I8").Offset(n, 0).Value = sqlmobilerateid.Value
        n = n + 1
        End If
        sql.MoveNext
        Loop
        sql.Close
      
    Set clientRS = Nothing
    Set csBook = Nothing
    Set xlApp = Nothing
    Set sql = Nothing
    

End Sub

[davegugg]

I believe you have two options:

1) Change the definition of SQLQuery ("SELECT tblMobileDetail.MobileID,...") to a pre-defined query in the database. Access can do a query on a predefined query, but not on a recordset in memory.

2) Create a temporary table to hold the results of SQLQuery, then delete it when you are finished.

I can't really understand why you are trying to set up a new query called sqlquerytotal, because it contains the exact same data as your SQLQuery.