Create a Temp table to create Totals Query

[Rx_]

Subroutine to create a Temp Table - Then a Function that runs a total on the Temp Table to populate 3 fields.

My listbox shows a 12 rolling total of records starting from a combobox Year and a combobox month. ((DateSerial([CalYear],[mo],1)) is used with a decending order to populate the 12 records in the listbox.
A Totals query doesn't like the DateSerial. Take the same SQL code used to generate the Listbox records, create a temp table, then use the Totals Query in a Recordset.


Step 1 - populate the listbox with 12 months

Private Sub lst_id_wells_AfterUpdate()  ' list box shows 12 months detail - text box shows the 12 month totals
          Dim SqlStrTank As String
          Dim SqlStrFlare As String
          Dim sqlBetween As String
10        On Error Resume Next
20        Me.ID_Well = Me.lst_id_wells
          ' Ed Emission Tanks top 12 query  Use a Combo box  to choose Year and Combo Box to choose Month for sqlBetween
30    sqlBetween = "Between #" & Me.lstED_EMMo & "/1/" & Me.lstCalYr & "# AND #" & (Me.lstED_EMMo - 1) & "/1/" & (Me.lstCalYr - 1) & "#"
40    SqlStrTank = "SELECT TOP 12 ED_EMTank.ID_Tank, ED_EMTank.ID_Wells, ED_EMTank.CalYear, ED_EMTank.CalMonth, ED_EMTank.Throughput, ED_EMTank.UnControlled, " & _
      "ED_EMTank.[Actual Controlled],  ED_EMTank.Controlled, ED_EMTank.Efficiency, DateSerial([CalYear],[mo],1) AS YrMoDay "
50    SqlStrTank = SqlStrTank & "FROM ED_EMTank "
60    SqlStrTank = SqlStrTank & "WHERE (((ED_EMTank.ID_Wells)=" & Me.ID_Well & ") AND ((DateSerial([CalYear],[mo],1))" & sqlBetween & ")) "
70    SqlStrTank = SqlStrTank & " ORDER BY DateSerial([CalYear],[mo],1) DESC"
80    Me.lstED_EM_Tank.RowSource = SqlStrTank
90    CreateTempTable SqlStrTank  ' create a temp table of the 12 records That will show up in the Listbox
100   TankTotals                  ' Run a Totals query on the new Temp table - populate the 3 text boxes
End Sub

Step 2 Create a Temp Table with the same SQL for the Listbox

Sub CreateTempTable(TempSQL As String)
10       On Error GoTo ErrorHandler
         Dim strSQL As String
         Dim strTable As String
20       strTable = "tblTempTable"
         'Delete the table if it exists
30       DoCmd.DeleteObject acTable, strTable
40    strSQL = "Select * INTO " & strTable & " From (" & TempSQL & ")"   'Select * Into ATempTable from ( ' sql string ' )
50    CurrentDb.Execute strSQL
      'Insert more code here to do something with temp table
60    Exit Sub
ErrorHandler:
70    If Err.Number = 7874 Then
80    Resume Next 'resume if temp table does not exist
90    End If
End Sub

Step 3 Run a Total Query and populate the 3 text boxes

Sub TankTotals()
Dim sqlBetween As String
Dim sqlSum As String
10 sqlSum = "SELECT Sum(ATempTable.Throughput) AS SumOfThroughput, Sum(ATempTable.UnControlled) AS SumOfUnControlled, Sum(ATempTable.[Actual Controlled]) AS [SumOfActual Controlled]"
20 sqlSum = sqlSum & "FROM ATempTable; "
Dim rstTankTotals As Recordset
30 Set rstTankTotals = Application.CurrentDb.OpenRecordset(sqlSum, dbOpenDynaset)
40 If rstTankTotals.RecordCount = 1 Then
50 Me.txtTankThroughputEdit = rstTankTotals.Fields(0).Value
60 Me.txtTankUnControlledEdit = rstTankTotals.Fields(1).Value
70 Me.txtTankActualControlEdit = rstTankTotals.Fields(2).Value
80 End If
90 Set rstTankTotals = Nothing
End Sub