Output report for each record in a table

Teddy V · Jul 26, 2011

My table structures:
Master: (one record per group)
Group
Group Description

CostCtr: (multiple Cost Center per group
Group
Cost Center
Cost Center Description

Detail Transactions: (multiple Transactions per Cost Center)
Cost Center
Account Number
Voucher
Amount
Date

I have a query that links these three files and receives selection criteria from a Form:
Date From
Date To
Group to Report

The Form also has input fields for the folder names of the Fiscal Year and Fiscal Month that are used to complete the location to store the resulting .pdf report. Also on the Form is a list box created from the Master file to aid in selecting the group to report. All has worked well up to now. What has happened is that the number of groups has grown to make single selection of each group too tedious. I have been trying to alter the code to loop through Master table and run the same report for each record but I'm at a loss to figure it out. I will post my current code (no snickering) that is a mix of bits and pieces from here and there.

Let me know if you need more information....

Private Sub Command3_Click()

Dim dbsCopy As DAO.Database
Dim rstGroups As DAO.Recordset
Dim intI As Integer
Dim strFY As String
Dim strFM As String
Dim strSelect As String
Dim strLocation As String
Dim strPath As String
Dim strReport As String
Dim strExt As String
Let strFY = [Forms]![Main]![txtFY] & "\"
Let strFM = [Forms]![Main]![txtFM] & "\"
Let strPath = "G:\Group\SMCACCT\PEGGY\SHELTER\EARN ANAL\"

Set dbsCopy = CurrentDb
Set rstGroups = dbsCopy.OpenRecordset("Master")

'If the recordset is empty, exit.
If rstGroups.EOF Then Exit Sub
intI = 1
With rstGroups
Do Until .EOF

Let strSelect = ![Group] & "\"
Let strPath = "G:\Group\SMCACCT\PEGGY\SHELTER\EARN ANAL\"
Let strReport = "CummTransHistory - " & ![Group]
Let strExt = ".pdf"
Let strLocation = strPath & strFY & strFM & strSelect & strReport & strExt

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("Cumm Trans History Query")
qdf.SQL = Replace(Replace(qdf.SQL, "Group", ![Group]))

DoCmd.OutputTo acOutputReport, "Cumm Trans History Report - Select a Group", "PDFFormat(*.pdf)", strLocation, False, "", 0, acExportQualityPrint

.MoveNext
intI = intI + 1
Loop
End With
rstGroups.Close
dbsCopy.Close
Set rstGroups = Nothing
Set dbsCopy = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Guus2005 · Jul 27, 2011

I believe it works only once. After that you have change the original query to which the report was attached and you can't replace "Group" because you changed it to rstGroups![Group]

Create a query "qryReportTemplate"

and change your code accordingly:

Code:

Private Sub Command3_Click()

    Dim intI        As Integer
    Dim strFY       As String
    Dim strFM       As String
    Dim strExt      As String
    Dim dbsCopy     As DAO.Database
    Dim strPath     As String
    Dim strSelect   As String
    Dim strReport   As String
    Dim rstGroups   As DAO.Recordset
    Dim strLocation As String
    Let strFY = [Forms]![Main]![txtFY] & "\"
    Let strFM = [Forms]![Main]![txtFM] & "\"
    Let strPath = "G:\Group\SMCACCT\PEGGY\SHELTER\EARN ANAL\"

    Set dbsCopy = CurrentDb
    Set rstGroups = dbsCopy.OpenRecordset("Master")


'If the recordset is empty, exit.
    If rstGroups.EOF Then Exit Sub
    intI = 1
    With rstGroups
        Do Until .EOF

            Let strSelect = ![Group] & "\"
            Let strPath = "G:\Group\SMCACCT\PEGGY\SHELTER\EARN ANAL\"
            Let strReport = "CummTransHistory - " & ![Group]
            Let strExt = ".pdf"
            Let strLocation = strPath & strFY & strFM & strSelect & strReport & strExt

            Dim qdf As QueryDef

            Set qdf = CurrentDb.QueryDefs("Cumm Trans History Query")
            [COLOR="Red"]qdf.SQL = Replace(CurrentDb.QueryDefs("qryReportTemplate").SQL, "Group", ![Group])[/COLOR]

            DoCmd.OutputTo acOutputReport, "Cumm Trans History Report - Select a Group", "PDFFormat(*.pdf)", strLocation, False, "", 0, acExportQualityPrint

            .MoveNext
            intI = intI + 1
        Loop
    End With
    rstGroups.Close
    dbsCopy.Close
    Set rstGroups = Nothing
    Set dbsCopy = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

... and i wouldn't use "Let"

Enjoy!

Teddy V · Jul 27, 2011

Thanks so much for the code!

Now the only problem is that I am getting a Run-time error '3075' at this statement:
qdf.SQL = Replace(CurrentDb.QueryDefs("qryReportTemplate").SQL, "Group", ![Group])

The name of the actual text field in the table CostCtr is "PrintGrp" so I
tried to fix it by adding the following lines of code to add quotes around ![Group]:

Dim selGroup As String
selGroup = "" & ![Group] & ""

and changed the qdf.SQL statement to be:
qdf.SQL = Replace(CurrentDb.QueryDefs("qryReportTemplate").SQL, "PrintGrp", selGroup)
but still get the error:
Invalid use of ',', '!', or '()' in query expression 'CostCtr.1862 = Master.Grou'.

I added a Watch on selGroup and when it failed again, the value was "1862" which is the correct value for the first record in table Master.

Any suggestions?

spikepl · Jul 27, 2011

As always , do one step at a time, instead of all in one go, hoping that it will work:

get hold of the SQL from the query def and debug.print it
do the Replace.
debug.print the new SQL and inspect it. If in doubt about syntax, put it into the SQLview of the query designer and test there
now plug the new sql string into the query def

Teddy V · Jul 27, 2011

Sorry for being a rookie at this. I know how to find the SQL for the qryReportTemplate but not how to debug.print it.

And something else to consider later is that I now realize I will have to append the ![Group] to the exsisting criteria of "<>"INACTIVE" And Is Not Null".

spikepl · Jul 27, 2011

Put

debug.print WhichEverString 'in the code where appropriate

run your application

press CTRL G and the output will be in the immediate window

For debugging in general, follow this guide: http://www.access-programmers.co.uk/forums/showthread.php?t=149429

Guus2005 · Jul 28, 2011

You have to make sure that the qryReportTemplate works and produces the output you want.
Make sure that when you replace the text "Group" by a value represented by ![Group] you dont accidently replace the "Group By" clause of the query, rendering the query useless.

Code:

select field1, field2 from Table1 group by field1, field2 having field2 = Group

After replacement ![Group] = "AWF"

Code:

select field1, field2 from Table1 [COLOR="Red"]'AWF'[/COLOR] by field1, field2 having field2 = 'AWF'

After replacement ![Group] = 40

Code:

select field1, field2 from Table1 [COLOR="red"]40[/COLOR] by field1, field2 having field2 = 40

That's why i always use a "Parameter" prefix: "ParameterGroup" to avoid these mix-ups.

Please follow spikepl's advice on debugging your app.

Share & Enjoy!

Teddy V · Jul 28, 2011

Guus2005 said:
You have to make sure that the qryReportTemplate works and produces the output you want.
Make sure that when you replace the text "Group" by a value represented by ![Group] you dont accidently replace the "Group By" clause of the query, rendering the query useless.

Code:

select field1, field2 from Table1 group by field1, field2 having field2 = Group

After replacement ![Group] = "AWF"

Code:

select field1, field2 from Table1 [COLOR=red]'AWF'[/COLOR] by field1, field2 having field2 = 'AWF'

After replacement ![Group] = 40

Code:

select field1, field2 from Table1 [COLOR=red]40[/COLOR] by field1, field2 having field2 = 40

That's why i always use a "Parameter" prefix: "ParameterGroup" to avoid these mix-ups.

Please follow spikepl's advice on debugging your app.

Share & Enjoy!

This seems to be the problem after trying the debug procedure spikepl suggested.

qdf.SQL = Replace(CurrentDb.QueryDefs("qryReportTemplate").SQL, "Group", ![Group]) is replacing the Master.Group in the FROM statement. I also have Master.Group in the SELECT statement.

What is the easiest way to get around this?

This has been a real learning experience, thank you!

spikepl · Jul 29, 2011

Don't do everything at once, but store the SQL in a string variable and operate on that string.

Play with the string functions http://www.developerbarn.com/microsoft-access/103-string-functions-listed-name.html

Using those you can cut bits out of your string and operate on one bit and using the concatenation operator & stitch the bits together again.

Guus2005 · Jul 29, 2011

As i said, i use a longer string which is not used anywhere else in the query: "ParameterGroup".
This string is quite unique throughout all of my queries.
Another example:

Code:

select * from table1 where Approved = ParameterApproved

While running this query it wil ask me the value for ParameterApproved (when table1 is an actual table and approved an actual field)

HTH

Output report for each record in a table

Teddy V

New member

Guus2005

AWF VIP

Teddy V

New member

spikepl

Eledittingent Beliped

Teddy V

New member

spikepl

Eledittingent Beliped

Guus2005

AWF VIP

Teddy V

New member

spikepl

Eledittingent Beliped

Guus2005

AWF VIP

Similar threads

Users who are viewing this thread