I have a module which outputs a .pdf file per loop out to a location. I am trying to modify the code below so that it outputs the object as an .html image instead. I am chnaging 2 lines of code to achieve this. The first is changing the forrmat type from:
'strOutputFormat = "PDF Format (*.pdf)"
to:
strOutputFormat = "HTML Format (*.html)"
The 2nd change is the output To statement from :
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".pdf", False, ""
to:
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".html", False, ""
the full code is listed below. The error I receive is the format bin which you are attempting to output the current object is not avaialble. Do I need to modify the syntax or upgrade my libraries? I am using Access 2007. Any help would be greatly appreciated.
----------------------------------------------------------------------------------------------------
Function ScorecardReports4()
Dim strOutputFormat As String
'strOutputFormat = "PDF Format (*.pdf)"
strOutputFormat = "HTML Format (*.html)"
Dim strObjectName As String
strObjectName = "BranchScorecardRprt"
'Reference to Current Database
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")
'Identify table with names
Dim tbl As TableDef
Set tbl = db.TableDefs("Branch")
'Declare a string variable to hold the Branch Name
Dim strBranchName As String
strBranchName = ""
'Declare a string variable to hold Division Name
Dim strDivisionName As String
strDivisionName = ""
'Declare a string variable to hold Region Name
Dim strRegionName As String
strRegionName = ""
'Declare a counter for a loop
Dim i As Integer
i = 0
'Open a recordset against the table with names
Dim rst As Recordset
Set rst = tbl.OpenRecordset
With rst
If .RecordCount > 0 Then 'Verify records even exist first!
.MoveFirst 'Moves to the first position
Do While i <= .RecordCount And Not .EOF
strBranchName = rst!BranchName
strDivisionName = rst!DivisionName
strRegionName = rst!RegionName
'Start First Routine Build BranchMasterTbl pulling all Actual and Budget data by Month and YTD Branch By Branch
Dim StrSQL As String
StrSQL = ""
StrSQL = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, Sum(TY.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY.[To Date $]) AS YTD, "
StrSQL = StrSQL & "Sum(TY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL = StrSQL & "Sum(TY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
StrSQL = StrSQL & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl "
StrSQL = StrSQL & "FROM ((Branch INNER JOIN TY ON Branch.Branch = TY.Div) INNER JOIN ChartOfAccounts ON TY.Account = ChartOfAccounts.Account) "
StrSQL = StrSQL & "INNER JOIN ReportGroup3 ON (ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) AND (TY.Ctr = ReportGroup3.Ctr) "
StrSQL = StrSQL & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL = StrSQL & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
StrSQL = StrSQL & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
StrSQL = StrSQL & "ORDER BY Branch.Branch, TY.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL
qdf.SQL = StrSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Second Routine Build BranchMasterTbl_LY_FY_Actual
Dim StrSQL2 As String
StrSQL2 = ""
StrSQL2 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, LY.Period, LY.Year, "
StrSQL2 = StrSQL2 & "LY.Account, LY.Ctr, LY.Type, LY.Description, Sum(LY.[Current Period $]) AS [SumOfCurrent Period $], Sum(LY.[To Date $]) AS YTD, "
StrSQL2 = StrSQL2 & "Sum(LY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(LY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL2 = StrSQL2 & "Sum(LY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(LY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, "
StrSQL2 = StrSQL2 & "ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_LY_FY_Actual "
StrSQL2 = StrSQL2 & "FROM (Branch INNER JOIN LY ON Branch.Branch = LY.Div) "
StrSQL2 = StrSQL2 & "INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) "
StrSQL2 = StrSQL2 & "ON (LY.Ctr = ReportGroup3.Ctr) AND (LY.Account = ChartOfAccounts.Account) "
StrSQL2 = StrSQL2 & "WHERE Branch.BranchName = """ & strBranchName & """ And ((LY.Period) = 12) And ((LY.Year) = 2011) And ((ChartOfAccounts.ScorecardLine) > 0) "
StrSQL2 = StrSQL2 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, LY.Period, LY.Year, "
StrSQL2 = StrSQL2 & "LY.Account, LY.Ctr, LY.Type, LY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
StrSQL2 = StrSQL2 & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL2 = StrSQL2 & "HAVING (((Branch.BranchName)= " & strBranchName & " And ((LY.Period) = 12) And ((LY.Year) = 2011) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL2 = StrSQL2 & "ORDER BY Branch.Branch, LY.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL2
qdf.SQL = StrSQL2
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Third Routine Build BranchMasterTbl_TY_FY_Budget
Dim StrSQL3 As String
StrSQL3 = ""
StrSQL3 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearCurrent.Period, "
StrSQL3 = StrSQL3 & "BudgetFullYearCurrent.Year, BudgetFullYearCurrent.Account, BudgetFullYearCurrent.Ctr, BudgetFullYearCurrent.Type, BudgetFullYearCurrent.Description, "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period $]) AS [SumOfCurrent Period $], Sum(BudgetFullYearCurrent.[To Date $]) AS YTD, "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(BudgetFullYearCurrent.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(BudgetFullYearCurrent.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
StrSQL3 = StrSQL3 & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_TY_FY_Budget "
StrSQL3 = StrSQL3 & "FROM (Branch INNER JOIN BudgetFullYearCurrent ON Branch.Branch = BudgetFullYearCurrent.Div) "
StrSQL3 = StrSQL3 & "INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (BudgetFullYearCurrent.Ctr = ReportGroup3.Ctr) "
StrSQL3 = StrSQL3 & "AND (BudgetFullYearCurrent.Account = ChartOfAccounts.Account) "
StrSQL3 = StrSQL3 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL3 = StrSQL3 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearCurrent.Period, "
StrSQL3 = StrSQL3 & "BudgetFullYearCurrent.Year, BudgetFullYearCurrent.Account, BudgetFullYearCurrent.Ctr, BudgetFullYearCurrent.Type, BudgetFullYearCurrent.Description, "
StrSQL3 = StrSQL3 & "ChartOfAccounts.ScorecardLine , ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
StrSQL3 = StrSQL3 & "ORDER BY Branch.Branch, BudgetFullYearCurrent.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL3
qdf.SQL = StrSQL3
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Fifth Routine Build OSHARprtTbl referenced in SubReport5
Dim StrSQL5 As String
StrSQL5 = ""
StrSQL5 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, OSHA_Archieve.Period, OSHA_Archieve.Year, OSHA_Archieve.Rate, "
StrSQL5 = StrSQL5 & "OSHA_Archieve.Rate_Benchmark INTO OSHARprtTbl "
StrSQL5 = StrSQL5 & "FROM Branch INNER JOIN OSHA_Archieve ON Branch.Branch = OSHA_Archieve.Branch "
StrSQL5 = StrSQL5 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL5 = StrSQL5 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, OSHA_Archieve.Period, OSHA_Archieve.Year, OSHA_Archieve.Rate, "
StrSQL5 = StrSQL5 & "OSHA_Archieve.Rate_Benchmark "
StrSQL5 = StrSQL5 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL5
qdf.SQL = StrSQL5
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Sixth Routine Build CustomerRprtTbl referenced in SubReport15
Dim StrSQL6 As String
StrSQL6 = ""
StrSQL6 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, CustomerLoyaltyArchieve.Period, "
StrSQL6 = StrSQL6 & "CustomerLoyaltyArchieve.Year, CustomerLoyaltyArchieve.Rate, CustomerLoyaltyArchieve.Rate_Benchmark INTO CustomerRprtTbl "
StrSQL6 = StrSQL6 & "FROM Branch INNER JOIN CustomerLoyaltyArchieve ON Branch.BranchName = CustomerLoyaltyArchieve.BranchName "
StrSQL6 = StrSQL6 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL6 = StrSQL6 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, CustomerLoyaltyArchieve.Period, "
StrSQL6 = StrSQL6 & "CustomerLoyaltyArchieve.Year, CustomerLoyaltyArchieve.Rate, CustomerLoyaltyArchieve.Rate_Benchmark "
StrSQL6 = StrSQL6 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL6
qdf.SQL = StrSQL6
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Seventh Routine Build BranchTbl used in all CRM Queries which are Unioned in CRMUnion Qry used in SubReport0
Dim StrSQL7 As String
StrSQL7 = ""
StrSQL7 = "SELECT Branch.Branch,"
StrSQL7 = StrSQL7 & "Branch.BranchName, Branch.Region, Branch.RegionName, Branch.Division, Branch.DivisionName, Branch.Operations "
StrSQL7 = StrSQL7 & "INTO BranchTbl "
StrSQL7 = StrSQL7 & "FROM Branch "
StrSQL7 = StrSQL7 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL7 = StrSQL7 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL7
qdf.SQL = StrSQL7
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Eighth Routine Build BranchMasterTbl_TY_FY_Budget_R used in ScorecardBranchQry_Crosstab_TY_FY_Budget_R
Dim StrSQL8 As String
StrSQL8 = ""
StrSQL8 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearRevised.Period, BudgetFullYearRevised.Year, BudgetFullYearRevised.Account, BudgetFullYearRevised.Ctr, BudgetFullYearRevised.Type, BudgetFullYearRevised.Description, Sum(BudgetFullYearRevised.[Current Period $]) AS [SumOfCurrent Period $], Sum(BudgetFullYearRevised.[To Date $]) AS YTD, Sum(BudgetFullYearRevised.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(BudgetFullYearRevised.[To Date Budget $]) AS [SumOfTo Date Budget $], Sum(BudgetFullYearRevised.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(BudgetFullYearRevised.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_TY_FY_Budget_R "
StrSQL8 = StrSQL8 & "FROM (Branch INNER JOIN BudgetFullYearRevised ON Branch.Branch = BudgetFullYearRevised.Div) INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (BudgetFullYearRevised.Account = ChartOfAccounts.Account) AND (BudgetFullYearRevised.Ctr = ReportGroup3.Ctr) "
StrSQL8 = StrSQL8 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL8 = StrSQL8 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearRevised.Period, BudgetFullYearRevised.Year, BudgetFullYearRevised.Account, BudgetFullYearRevised.Ctr, BudgetFullYearRevised.Type, BudgetFullYearRevised.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL8 = StrSQL8 & "HAVING (((Branch.BranchName) = [Forms]![checkmax]![cboBranchName]) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL8 = StrSQL8 & "ORDER BY Branch.Branch, BudgetFullYearRevised.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL8
qdf.SQL = StrSQL8
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Ninth Routine Build BranchMaster_OriginalBudgetTbl used in ScorecardBranchQry_Crosstab_Budget_O_YTD
Dim StrSQL9 As String
StrSQL9 = ""
StrSQL9 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY_OriginalBudget.Period, TY_OriginalBudget.Year, TY_OriginalBudget.Account, TY_OriginalBudget.Ctr, TY_OriginalBudget.Type, TY_OriginalBudget.Description, Sum(TY_OriginalBudget.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY_OriginalBudget.[To Date $]) AS YTD, Sum(TY_OriginalBudget.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY_OriginalBudget.[To Date Budget $]) AS [SumOfTo Date Budget $], Sum(TY_OriginalBudget.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY_OriginalBudget.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMaster_OriginalBudgetTbl "
StrSQL9 = StrSQL9 & "FROM (Branch INNER JOIN TY_OriginalBudget ON Branch.Branch = TY_OriginalBudget.Div) INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (TY_OriginalBudget.Account = ChartOfAccounts.Account) AND (TY_OriginalBudget.Ctr = ReportGroup3.Ctr) "
StrSQL9 = StrSQL9 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL9 = StrSQL9 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY_OriginalBudget.Period, TY_OriginalBudget.Year, TY_OriginalBudget.Account, TY_OriginalBudget.Ctr, TY_OriginalBudget.Type, TY_OriginalBudget.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL9 = StrSQL9 & "HAVING (((Branch.BranchName) = [Forms]![checkmax]![cboBranchName]) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL9 = StrSQL9 & "ORDER BY Branch.Branch, TY_OriginalBudget.Ctr, ChartOfAccounts.ScorecardLine; "
qdf.SQL = StrSQL9
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
' Output the reports to the Network Drive
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Analysis and Reports\JS\Scorecard\Reports\BranchScorecardRprt" & "-" & strBranchName & ".pdf", False, ""
'This Outputs to Division and Region Specific Brickcorpdata locations
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\" & strDivisionName & "\" & strRegionName & "\BranchScorecardRprt-" & strBranchName & ".pdf", False, ""
'This Outputs to Division and Region Specific Bricknet locations
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".html", False, ""
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\Branch Scorecards\" & strDivisionName & "\" & strRegionName & "\BranchScorecardRprt-" & strBranchName & ".xls", False, ""
i = i + 1
.MoveNext
Loop
Else
'Do Nothing
End If
End With
Debug.Print strBranchName
End Function
------------------------------------------------------------------------------------------------------
'strOutputFormat = "PDF Format (*.pdf)"
to:
strOutputFormat = "HTML Format (*.html)"
The 2nd change is the output To statement from :
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".pdf", False, ""
to:
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".html", False, ""
the full code is listed below. The error I receive is the format bin which you are attempting to output the current object is not avaialble. Do I need to modify the syntax or upgrade my libraries? I am using Access 2007. Any help would be greatly appreciated.
----------------------------------------------------------------------------------------------------
Function ScorecardReports4()
Dim strOutputFormat As String
'strOutputFormat = "PDF Format (*.pdf)"
strOutputFormat = "HTML Format (*.html)"
Dim strObjectName As String
strObjectName = "BranchScorecardRprt"
'Reference to Current Database
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")
'Identify table with names
Dim tbl As TableDef
Set tbl = db.TableDefs("Branch")
'Declare a string variable to hold the Branch Name
Dim strBranchName As String
strBranchName = ""
'Declare a string variable to hold Division Name
Dim strDivisionName As String
strDivisionName = ""
'Declare a string variable to hold Region Name
Dim strRegionName As String
strRegionName = ""
'Declare a counter for a loop
Dim i As Integer
i = 0
'Open a recordset against the table with names
Dim rst As Recordset
Set rst = tbl.OpenRecordset
With rst
If .RecordCount > 0 Then 'Verify records even exist first!
.MoveFirst 'Moves to the first position
Do While i <= .RecordCount And Not .EOF
strBranchName = rst!BranchName
strDivisionName = rst!DivisionName
strRegionName = rst!RegionName
'Start First Routine Build BranchMasterTbl pulling all Actual and Budget data by Month and YTD Branch By Branch
Dim StrSQL As String
StrSQL = ""
StrSQL = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, Sum(TY.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY.[To Date $]) AS YTD, "
StrSQL = StrSQL & "Sum(TY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL = StrSQL & "Sum(TY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
StrSQL = StrSQL & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl "
StrSQL = StrSQL & "FROM ((Branch INNER JOIN TY ON Branch.Branch = TY.Div) INNER JOIN ChartOfAccounts ON TY.Account = ChartOfAccounts.Account) "
StrSQL = StrSQL & "INNER JOIN ReportGroup3 ON (ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) AND (TY.Ctr = ReportGroup3.Ctr) "
StrSQL = StrSQL & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL = StrSQL & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
StrSQL = StrSQL & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
StrSQL = StrSQL & "ORDER BY Branch.Branch, TY.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL
qdf.SQL = StrSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Second Routine Build BranchMasterTbl_LY_FY_Actual
Dim StrSQL2 As String
StrSQL2 = ""
StrSQL2 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, LY.Period, LY.Year, "
StrSQL2 = StrSQL2 & "LY.Account, LY.Ctr, LY.Type, LY.Description, Sum(LY.[Current Period $]) AS [SumOfCurrent Period $], Sum(LY.[To Date $]) AS YTD, "
StrSQL2 = StrSQL2 & "Sum(LY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(LY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL2 = StrSQL2 & "Sum(LY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(LY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, "
StrSQL2 = StrSQL2 & "ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_LY_FY_Actual "
StrSQL2 = StrSQL2 & "FROM (Branch INNER JOIN LY ON Branch.Branch = LY.Div) "
StrSQL2 = StrSQL2 & "INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) "
StrSQL2 = StrSQL2 & "ON (LY.Ctr = ReportGroup3.Ctr) AND (LY.Account = ChartOfAccounts.Account) "
StrSQL2 = StrSQL2 & "WHERE Branch.BranchName = """ & strBranchName & """ And ((LY.Period) = 12) And ((LY.Year) = 2011) And ((ChartOfAccounts.ScorecardLine) > 0) "
StrSQL2 = StrSQL2 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, LY.Period, LY.Year, "
StrSQL2 = StrSQL2 & "LY.Account, LY.Ctr, LY.Type, LY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
StrSQL2 = StrSQL2 & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL2 = StrSQL2 & "HAVING (((Branch.BranchName)= " & strBranchName & " And ((LY.Period) = 12) And ((LY.Year) = 2011) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL2 = StrSQL2 & "ORDER BY Branch.Branch, LY.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL2
qdf.SQL = StrSQL2
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Third Routine Build BranchMasterTbl_TY_FY_Budget
Dim StrSQL3 As String
StrSQL3 = ""
StrSQL3 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearCurrent.Period, "
StrSQL3 = StrSQL3 & "BudgetFullYearCurrent.Year, BudgetFullYearCurrent.Account, BudgetFullYearCurrent.Ctr, BudgetFullYearCurrent.Type, BudgetFullYearCurrent.Description, "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period $]) AS [SumOfCurrent Period $], Sum(BudgetFullYearCurrent.[To Date $]) AS YTD, "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(BudgetFullYearCurrent.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL3 = StrSQL3 & "Sum(BudgetFullYearCurrent.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(BudgetFullYearCurrent.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
StrSQL3 = StrSQL3 & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_TY_FY_Budget "
StrSQL3 = StrSQL3 & "FROM (Branch INNER JOIN BudgetFullYearCurrent ON Branch.Branch = BudgetFullYearCurrent.Div) "
StrSQL3 = StrSQL3 & "INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (BudgetFullYearCurrent.Ctr = ReportGroup3.Ctr) "
StrSQL3 = StrSQL3 & "AND (BudgetFullYearCurrent.Account = ChartOfAccounts.Account) "
StrSQL3 = StrSQL3 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL3 = StrSQL3 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearCurrent.Period, "
StrSQL3 = StrSQL3 & "BudgetFullYearCurrent.Year, BudgetFullYearCurrent.Account, BudgetFullYearCurrent.Ctr, BudgetFullYearCurrent.Type, BudgetFullYearCurrent.Description, "
StrSQL3 = StrSQL3 & "ChartOfAccounts.ScorecardLine , ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
StrSQL3 = StrSQL3 & "ORDER BY Branch.Branch, BudgetFullYearCurrent.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL3
qdf.SQL = StrSQL3
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Fifth Routine Build OSHARprtTbl referenced in SubReport5
Dim StrSQL5 As String
StrSQL5 = ""
StrSQL5 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, OSHA_Archieve.Period, OSHA_Archieve.Year, OSHA_Archieve.Rate, "
StrSQL5 = StrSQL5 & "OSHA_Archieve.Rate_Benchmark INTO OSHARprtTbl "
StrSQL5 = StrSQL5 & "FROM Branch INNER JOIN OSHA_Archieve ON Branch.Branch = OSHA_Archieve.Branch "
StrSQL5 = StrSQL5 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL5 = StrSQL5 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, OSHA_Archieve.Period, OSHA_Archieve.Year, OSHA_Archieve.Rate, "
StrSQL5 = StrSQL5 & "OSHA_Archieve.Rate_Benchmark "
StrSQL5 = StrSQL5 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL5
qdf.SQL = StrSQL5
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Sixth Routine Build CustomerRprtTbl referenced in SubReport15
Dim StrSQL6 As String
StrSQL6 = ""
StrSQL6 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, CustomerLoyaltyArchieve.Period, "
StrSQL6 = StrSQL6 & "CustomerLoyaltyArchieve.Year, CustomerLoyaltyArchieve.Rate, CustomerLoyaltyArchieve.Rate_Benchmark INTO CustomerRprtTbl "
StrSQL6 = StrSQL6 & "FROM Branch INNER JOIN CustomerLoyaltyArchieve ON Branch.BranchName = CustomerLoyaltyArchieve.BranchName "
StrSQL6 = StrSQL6 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL6 = StrSQL6 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, CustomerLoyaltyArchieve.Period, "
StrSQL6 = StrSQL6 & "CustomerLoyaltyArchieve.Year, CustomerLoyaltyArchieve.Rate, CustomerLoyaltyArchieve.Rate_Benchmark "
StrSQL6 = StrSQL6 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL6
qdf.SQL = StrSQL6
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Seventh Routine Build BranchTbl used in all CRM Queries which are Unioned in CRMUnion Qry used in SubReport0
Dim StrSQL7 As String
StrSQL7 = ""
StrSQL7 = "SELECT Branch.Branch,"
StrSQL7 = StrSQL7 & "Branch.BranchName, Branch.Region, Branch.RegionName, Branch.Division, Branch.DivisionName, Branch.Operations "
StrSQL7 = StrSQL7 & "INTO BranchTbl "
StrSQL7 = StrSQL7 & "FROM Branch "
StrSQL7 = StrSQL7 & "WHERE Branch.BranchName= """ & strBranchName & """ "
StrSQL7 = StrSQL7 & "ORDER BY Branch.Branch; "
Debug.Print StrSQL7
qdf.SQL = StrSQL7
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Eighth Routine Build BranchMasterTbl_TY_FY_Budget_R used in ScorecardBranchQry_Crosstab_TY_FY_Budget_R
Dim StrSQL8 As String
StrSQL8 = ""
StrSQL8 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearRevised.Period, BudgetFullYearRevised.Year, BudgetFullYearRevised.Account, BudgetFullYearRevised.Ctr, BudgetFullYearRevised.Type, BudgetFullYearRevised.Description, Sum(BudgetFullYearRevised.[Current Period $]) AS [SumOfCurrent Period $], Sum(BudgetFullYearRevised.[To Date $]) AS YTD, Sum(BudgetFullYearRevised.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(BudgetFullYearRevised.[To Date Budget $]) AS [SumOfTo Date Budget $], Sum(BudgetFullYearRevised.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(BudgetFullYearRevised.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl_TY_FY_Budget_R "
StrSQL8 = StrSQL8 & "FROM (Branch INNER JOIN BudgetFullYearRevised ON Branch.Branch = BudgetFullYearRevised.Div) INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (BudgetFullYearRevised.Account = ChartOfAccounts.Account) AND (BudgetFullYearRevised.Ctr = ReportGroup3.Ctr) "
StrSQL8 = StrSQL8 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL8 = StrSQL8 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, BudgetFullYearRevised.Period, BudgetFullYearRevised.Year, BudgetFullYearRevised.Account, BudgetFullYearRevised.Ctr, BudgetFullYearRevised.Type, BudgetFullYearRevised.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL8 = StrSQL8 & "HAVING (((Branch.BranchName) = [Forms]![checkmax]![cboBranchName]) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL8 = StrSQL8 & "ORDER BY Branch.Branch, BudgetFullYearRevised.Ctr, ChartOfAccounts.ScorecardLine; "
Debug.Print StrSQL8
qdf.SQL = StrSQL8
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
'Start Ninth Routine Build BranchMaster_OriginalBudgetTbl used in ScorecardBranchQry_Crosstab_Budget_O_YTD
Dim StrSQL9 As String
StrSQL9 = ""
StrSQL9 = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY_OriginalBudget.Period, TY_OriginalBudget.Year, TY_OriginalBudget.Account, TY_OriginalBudget.Ctr, TY_OriginalBudget.Type, TY_OriginalBudget.Description, Sum(TY_OriginalBudget.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY_OriginalBudget.[To Date $]) AS YTD, Sum(TY_OriginalBudget.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY_OriginalBudget.[To Date Budget $]) AS [SumOfTo Date Budget $], Sum(TY_OriginalBudget.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY_OriginalBudget.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMaster_OriginalBudgetTbl "
StrSQL9 = StrSQL9 & "FROM (Branch INNER JOIN TY_OriginalBudget ON Branch.Branch = TY_OriginalBudget.Div) INNER JOIN (ChartOfAccounts INNER JOIN ReportGroup3 ON ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) ON (TY_OriginalBudget.Account = ChartOfAccounts.Account) AND (TY_OriginalBudget.Ctr = ReportGroup3.Ctr) "
StrSQL9 = StrSQL9 & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "
StrSQL9 = StrSQL9 & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY_OriginalBudget.Period, TY_OriginalBudget.Year, TY_OriginalBudget.Account, TY_OriginalBudget.Ctr, TY_OriginalBudget.Type, TY_OriginalBudget.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
'StrSQL9 = StrSQL9 & "HAVING (((Branch.BranchName) = [Forms]![checkmax]![cboBranchName]) And ((ChartOfAccounts.ScorecardLine) > 0)) "
StrSQL9 = StrSQL9 & "ORDER BY Branch.Branch, TY_OriginalBudget.Ctr, ChartOfAccounts.ScorecardLine; "
qdf.SQL = StrSQL9
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
' Output the reports to the Network Drive
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Analysis and Reports\JS\Scorecard\Reports\BranchScorecardRprt" & "-" & strBranchName & ".pdf", False, ""
'This Outputs to Division and Region Specific Brickcorpdata locations
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\" & strDivisionName & "\" & strRegionName & "\BranchScorecardRprt-" & strBranchName & ".pdf", False, ""
'This Outputs to Division and Region Specific Bricknet locations
DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\performancemanagement\Branch Scorecards\Image Files\BranchScorecardRprt-" & strBranchName & ".html", False, ""
'DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "http:\\bricknet\Branch Scorecards\" & strDivisionName & "\" & strRegionName & "\BranchScorecardRprt-" & strBranchName & ".xls", False, ""
i = i + 1
.MoveNext
Loop
Else
'Do Nothing
End If
End With
Debug.Print strBranchName
End Function
------------------------------------------------------------------------------------------------------