What should be multiple reports is becoming one report with multiple pages 1

[breezett93]

I have a query that pulls all records from a year provided by a user, qryIvcByYear. Then a report, IvcRpt, should be created for each record found. The report has a query in the record source to pull the relevant info related to the original record.

Instead, all records are being dumped into one report. My msgbox check confirms that a second report is never created.

Dim dbs As Database
Dim rst As Recordset
Dim strSelect As String
Dim strYear As String
Dim strIvcId As String

Set dbs = CurrentDb

strYear = InputBox("Please enter the year of invoices to be archived.")

strSelect = ("SELECT* FROM qryIvcByYear WHERE Year([IvcDt]) = " & strYear & "")

  'view recordset in query
Set rst = dbs.OpenRecordset(strSelect, dbOpenSnapshot)

    If rst.EOF Then
        Exit Sub
    Else
      rst.MoveFirst
      Do Until rst.EOF
        strIvcId = rst!IvcID
        MsgBox (strIvcId)
        DoCmd.OutputTo acOutputReport, "IvcRpt01", acFormatPDF, "ServerLocation\" & strYear & "\" & strIvcId & ".pdf"
        rst.MoveNext
    Loop
    End If

    rst.Close
    Set dbs = Nothing

 

[dhookom]

I would probably change the SQL property of the query that is the recordsource of the report. There is code faq701-7433 that shows how you can update the query to include the IvcID value.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

This is the recordsource for the report:

SELECT IvcTbl.*, OrdTbl.CustJobNo, OrdTbl.MiscChgCode AS MiscChgCode_OrdTbl FROM IvcTbl LEFT JOIN OrdTbl ON IvcTbl.OrdId = OrdTbl.OrdId;

I guess the problem then is that the join is on OrdId and not IvcId. My original query, qryIvcByYear, already has both IvcTbl and OrdTbl in the design.

I'm wary of changing the recordsource because the same report is used elsewhere in our system. Perhaps the solution is to copy my report and then adjust the recordsource?

 

[dhookom]

Create a saved query [qselIvcRpt01] with this SQL:

SELECT IvcTbl.*, OrdTbl.CustJobNo, OrdTbl.MiscChgCode AS MiscChgCode_OrdTbl FROM IvcTbl LEFT JOIN OrdTbl ON IvcTbl.OrdId = OrdTbl.OrdId;

Create a new module with the fChangeSQL function I suggested. Save the module with the name [modQueryCode].

Then add this code which assumes IvcID is numeric. If it is string you will need to add some quotes:

    'previous lines
[highlight #FCE94F]    Dim strNewSQL as String
    Dim strPrevSQL as String[/highlight]
    Do Until rst.EOF
        strIvcId = rst!IvcID
[highlight #FCE94F]        strNewSQL = "SELECT IvcTbl.*, OrdTbl.CustJobNo, OrdTbl.MiscChgCode AS MiscChgCode_OrdTbl " & _
                    "FROM IvcTbl LEFT JOIN OrdTbl ON IvcTbl.OrdId = OrdTbl.OrdId " & _
                    "WHERE IvcID = " & strIvcId 
        strOldSQL = fChangeSQL("qselIvcRpt01", strNewSQL )
        debug.Print strNewSQL
[/highlight]
        MsgBox (strIvcId)
        DoCmd.OutputTo acOutputReport, "IvcRpt01", acFormatPDF, "ServerLocation\" & strYear & "\" & strIvcId & ".pdf"
        rst.MoveNext
    Loop
    'following lines

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Before I begin testing, I wanted to post how I put it all together.

Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    
    Dim strNewSQL As String
    Dim strPrevSQL As String
    Do Until rst.EOF
        strIvcId = rst!IvcID
        strNewSQL = "SELECT IvcTbl.*, OrdTbl.CustJobNo, OrdTbl.MiscChgCode AS MiscChgCode_OrdTbl " & _
                    "FROM IvcTbl LEFT JOIN OrdTbl ON IvcTbl.OrdId = OrdTbl.OrdId " & _
                    "WHERE IvcID = " & strIvcId
        strOldSQL = fChangeSQL("qselIvcRpt01", strNewSQL)
        Debug.Print strNewSQL

        MsgBox (strIvcId)
        DoCmd.OutputTo acOutputReport, "IvcRpt01", acFormatPDF, "ServerLocation\" & strYear & "\" & strIvcId & ".pdf"
        rst.MoveNext
    Loop
    
    Set db = CurrentDb
    Set qd = db.QueryDefs(pstrQueryName)
    fChangeSQL = qd.sql
    qd.sql = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

 

[dhookom]

Does your code compile? I’m not sure why you added the extra lines of code at the end.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

The code at the end is the other half of the function you provided. Your previous post has 'previous lines and 'following lines'; so I thought I had to insert in the middle.

Code does not compile because a different module has an unrelated issue. It doesn't affect daily use of our database; so I do my testing on a test database as if it was live.

 

[dhookom]

The function I suggested was stand alone and needed no additions. The code I wanted you to insert was for your code.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Just clarifying that strOldSQL should be strPrevSQL? Or do I need to add another declaration?

 

[dhookom]

You are correct

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Sorry for the long delay. After making the necessary changes, only one report with many pages is being made. No errors are thrown.

Here is the entire sub:

Private Sub btnIvcArchive_Click()
Dim dbs As Database
Dim rst As Recordset
Dim strSelect As String
Dim strYear As String
Dim strIvcId As String
Dim strNewSQL As String
Dim strPrevSQL As String

Set dbs = CurrentDb

strYear = InputBox("Please enter the year of invoices to be archived.")

strSelect = ("SELECT* FROM qryIvcByYear WHERE Year([IvcDt]) = '" & strYear & "'")
Set rst = dbs.OpenRecordset(strSelect, dbOpenSnapshot)

  'view recordset in query
Do Until rst.EOF
      strIvcId = rst!IvcID
      strNewSQL = "SELECT IvcTbl.*, OrdTbl.CustJobNo, OrdTbl.MiscChgCode AS MiscChgCode_OrdTbl " & _
                  "FROM IvcTbl LEFT JOIN OrdTbl ON IvcTbl.OrdId = OrdTbl.OrdId " & _
                  "WHERE IvcID = " & strIvcId
      strPrevSQL = fChangeSQL("qselIvcRpt01", strNewSQL)
      Debug.Print strNewSQL

      MsgBox (strIvcId)
      DoCmd.OutputTo acOutputReport, "IvcRpt01", acFormatPDF, "ServerLocation\" & strYear & "\" & strIvcId & ".pdf"
      rst.MoveNext
Loop

    rst.Close
    Set dbs = Nothing
End Sub

 

[dhookom]

These are the typical troubleshooting steps I would take:
[ul]
[li]Can you confirm [qselIvcRpt01] is the record source for your report?[/li]
[li]Did you look into the immediate/debug window to check the values of strNewSQL?[/li]
[li]Have tried setting a breakpoint and stepping through your code?[/li]
[li]Did you check the SQL property of qselIvcRpt01 before and after running this code?[/li]
[/ul]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Updating the record source on the report seems to have done the trick. I did a small test and it worked. I'll run a larger test with the user that needs this, but I should be good for now.

Thank you so much!

 

[dhookom]

Good to hear. Please mark a post if it correctly resolves your question so people will know it's fixed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016