Customizing Output To Object Location 3

[JStrand]

I have code program running through a list of business units (branches) in a loop. After completing one loop I have the outputTo method writing the report to a location.

I am trying to construct the OutPut To method so I can customize the directory where the output is written based on it's Division and Region. The rst used in the routine are picking up the variables used in the OutPut To method. I believe the problem is in the syntax used in passing the Division, and Region variables to the OutPut To Method.

When I execute the code with the custom location syntax I receive a Run-time error '13': Type mismatch.

If you could take a look at the Output To syntax near the end of the Code below and see if you can trouble shoot I would greatly appreciate it. Thank you in advance.


Option Compare Database
Option Explicit

Function ScorecardReports()

Dim strOutputFormat As String
strOutputFormat = "PDF Format (*.pdf)"

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
Dim rst2 As Recordset
Dim rst3 As Recordset

Set rst = tbl.OpenRecordset
Set rst2 = tbl.OpenRecordset
Set rst3 = 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 = rst2!DivisionName
strRegionName = rst3!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

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\ " & strDivisionName & " & " \ " & strRegionName & " \ " & BranchScorecardRprt" & "-" & strBranchName & ".pdf", False, ""


i = i + 1

.MoveNext

Loop

Else
'Do Nothing

End If
End With

Debug.Print strBranchName

End Function

 

[PWise]

Try

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\ " & strDivisionName &  " \ " & strRegionName & " \ " & BranchScorecardRprt & "-" & strBranchName & ".pdf", False, ""

 

[JStrand]

Thank you PWise. I am getting a variable not defined error on [BranchScorecardRprt]. Still must be sytax issue as this variable is defined and worked with previous Output To method below.

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Analysis and Reports\JS\Scorecard\Reports\BranchScorecardRprt" & "-" & strBranchName & ".pdf", False,

 

[PWise]

Try

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\ " & strDivisionName & " \ " & strRegionName & " \BranchScorecardRprt-" & strBranchName & ".pdf", False,

 

[Andrzejek]

Also, use either:

With rst
    If .RecordCount > 0 Then   
        .MoveFirst  
        Do While Not .EOF[red]
or[/red]
        Do While i <= .RecordCount
...

but you do not have to use both at the same time.

Have fun.

---- Andy

 

[JStrand]

Thank you both.

I had to remove the added space before and after the backslashes from PWise's statement and this now completes the custom location naming syntax.

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "\\Brickcorpdata\accounting_gaithersburg\Scorecard\" & strDivisionName & "\" & strRegionName & "\BranchScorecardRprt-" & strBranchName & ".pdf", False, ""

New Problem with record set loop

I've now noticed a new problem that my method for assigning a variable isn't working in the second iteration of the counter loop as when the branch table gets to the next region it remains at the first division and branch. Can you see a problem with the manner in which I set up the :

Open a recordset against the table with names
Dim rst As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset

Set rst = tbl.OpenRecordset
Set rst2 = tbl.OpenRecordset
Set rst3 = 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 = rst2!DivisionName
strRegionName = rst3!RegionName

 

[PHV]

Use a single recordset !
No needs for rst2 nor rst3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JStrand]

Thank You PHV. Problem solved!