I'm trying to create a report based on two recordsets. I would like to display all the records in both recordsets, however, the report is only displaying the last record from each recordset. All records from recordset #1 MUST display in the report and the records from recordset #2 will display based on the Project/Task code in recordset #1. How can I display all the records from both recordsets and ensure each item in recordset #2 is matched with the appropriate Project/Task in recordset #1? Attached is the code I have that isn't working ....
P.S. I already viewed some of the posts and have tried some suggestions, but I'm still getting the same results. BTW, I'm using Access 2000 (not sure if this makes a difference with anything.)
Thanks in advance.
Nicole :-{}
P.S. I already viewed some of the posts and have tried some suggestions, but I'm still getting the same results. BTW, I'm using Access 2000 (not sure if this makes a difference with anything.)
Thanks in advance.
Nicole :-{}
Code:
Private Sub GetReportTotals()
On Error GoTo Err_0973Report
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
'Estimate
strSQL = "TRANSFORM Sum(Round([curEstimate])) AS Estimate"
strSQL = strSQL & " SELECT Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000') AS [Project/Task]"
strSQL = strSQL & " FROM tblProject INNER JOIN (tblFYMonth INNER JOIN tblAcct0973 ON tblFYMonth.intFYMonth = tblAcct0973.intFYMonth) ON (tblProject.intTaskCode = tblAcct0973.intTaskCode) AND (tblProject.intProjectCode = tblAcct0973.intProjectCode)"
strSQL = strSQL & " GROUP BY Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000'), tblAcct0973.intProjectCode, tblAcct0973.intTaskCode"
strSQL = strSQL & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"
'Used
strSQL2 = "TRANSFORM Sum(Round([curAmount])) AS SumTotal"
strSQL2 = strSQL2 & " SELECT Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000') AS [Project/Task], tblExpense.intObjectGroup, Sum(Round([curAmount])) AS TotalOfcurAmount"
strSQL2 = strSQL2 & " FROM tblProject INNER JOIN (tblObjectGroup INNER JOIN (tblFYMonth INNER JOIN tblExpense ON tblFYMonth.intFYMonth = tblExpense.intFYMonth) ON tblObjectGroup.intObjectGroup = tblExpense.intObjectGroup) ON (tblProject.intTaskCode = tblExpense.intTaskCode) AND (tblProject.intProjectCode = tblExpense.intProjectCode)"
strSQL2 = strSQL2 & " WHERE (((tblExpense.intObjectGroup) = 52060300) And ((Left$([intBranchCode], 2)) = 60))"
strSQL2 = strSQL2 & " GROUP BY Format([tblProject.intProjectCode],'0000000') & '-' & Format([tblProject.intTaskCode],'000'), tblExpense.intObjectGroup, tblExpense.intProjectCode, tblExpense.intTaskCode"
strSQL2 = strSQL2 & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Set rst2 = CurrentDb.OpenRecordset(strSQL2, dbOpenSnapshot)
'Estimate
With rst
.MoveFirst
While Not .EOF
Me.ProjectTaskEstimate = rst("Project/Task")
Me.OctEstimate = Nz(rst("Oct"), 0)
Me.NovEstimate = Nz(rst("Nov"), 0)
Me.DecEstimate = Nz(rst("Dec"), 0)
Me.JanEstimate = Nz(rst("Jan"), 0)
Me.FebEstimate = Nz(rst("Feb"), 0)
Me.MarEstimate = Nz(rst("Mar"), 0)
Me.AprEstimate = Nz(rst("Apr"), 0)
Me.MayEstimate = Nz(rst("May"), 0)
Me.JunEstimate = Nz(rst("Jun"), 0)
Me.JulEstimate = Nz(rst("Jul"), 0)
Me.AugEstimate = Nz(rst("Aug"), 0)
Me.SepEstimate = Nz(rst("Sep"), 0)
.MoveNext
Wend
End With
'Used
With rst2
.MoveFirst
While Not .EOF
Me.ProjectTaskUsed = rst2("Project/Task")
Me.OctUsed = Nz(rst2("Oct"), 0)
Me.NovUsed = Nz(rst2("Nov"), 0)
Me.DecUsed = Nz(rst2("Dec"), 0)
Me.JanUsed = Nz(rst2("Jan"), 0)
Me.FebUsed = Nz(rst2("Feb"), 0)
Me.MarUsed = Nz(rst2("Mar"), 0)
Me.AprUsed = Nz(rst2("Apr"), 0)
Me.MayUsed = Nz(rst2("May"), 0)
Me.JunUsed = Nz(rst2("Jun"), 0)
Me.JulUsed = Nz(rst2("Jul"), 0)
Me.AugUsed = Nz(rst2("Aug"), 0)
Me.SepUsed = Nz(rst2("Sep"), 0)
.MoveNext
Wend
End With
Exit_0973Report:
rst2.Close
rst.Close
Exit Sub
Err_0973Report:
MsgBox Err.Description
Resume Exit_0973Report
End Sub