DoCmd.OutputTo acOutputReport Fail (1 Viewer)

[jsdba]

Happy Holidays Experts,

I have some code that has been working for years but new business rules required me to make to some updates. I have some code that creates a report (expense invoice) and export it to PDF via DoCmd.OutputTo acOutputReport. New business rules now require me to capture some data from the report as its being created which I'm doing in the report groupheader_print. Below is the order in which the code should work:

1. Open report (works)
   

   DoCmd.OpenReport rptName, acViewPreview, , "ProjectNum='" & ProjectNum & "' AND ClientID=" & ClientID, acHidden

2. Run code to capture data (works)
   

   Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
   'Add expense record to Invoice Archive
   Dim db As Database
   Dim rs As Recordset
   Dim rs1 As Recordset
   Set db = CurrentDb
   
   Set rs = db.OpenRecordset("SELECT * FROM qryInvoiceArchiveExpense WHERE ClientID=" & Me.ClientID & "AND ProjectNum='" & Me.ProjectNum & "'", dbOpenDynaset, dbSeeChanges)
   
   Set rs1 = db.OpenRecordset("tblFeeServiceInvoiceExpense", dbOpenDynaset, dbSeeChanges)
   
   Do Until rs.EOF
       With rs1
           .AddNew
               !ExpenseID = rs.Fields("ExpenseID")
               !InvoiceNum = IncrementalInvoiceNum
               !QtyCompleted = rs.Fields("ExpenseQty")
               !rTotal = rs.Fields("ExpenseAmountTotal")
               !ClientID = rs.Fields("ClientID")
               !ProjectNum = rs.Fields("ProjectNum")
           .Update
           rs.MoveNext
       End With
   Loop
   rs.Close
   rs1.Close
   
   Set rs = Nothing
   Set rs1 = Nothing
   Set db = Nothing
   End Sub

3. DoCmd.OutputTo acOutputReport (code quits here after i see the pop showing the export is happening. report is not exported. there is no error msg)
   

   DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPathAttach, showpdf

4. Run some other misc code to do some updates
5. close report
6. Loop to the next record
7. Repeat at #1

Note: If i remove the #2 everything works as it should. Like i said this code has been working for years. I'm clueless here.

 

[June7]

Step 2 should actually not work properly because recordset rs SQL has syntax error. Need a separator space in front of "AND ProjectNum=". Should be " AND ProjectNum="

 

[theDBguy]

Hi. Your step #2 seems to be adding some new records to a table. Are you sure you can't do that outside of the report? The report is opened filtered to a specific client. Can't you do the same using a query for your step #2, instead of a report? Just curious...

 

[jsdba]

Step 2 should actually not work properly because recordset rs SQL has syntax error. Need a separator space in front of "AND ProjectNum=". Should be " AND ProjectNum="

Thank you but #2 works fine.

 

[June7]

Well, that is odd because the SQL shown should not open with that filter criteria. Without space, ClientID value will run into "AND" and the WHERE clause will be gibberish.

Have you step debugged?

Where does IncrementalInvoiceNum value come from? Is that a field or control name?

Why do you need to save aggregate data?

Re Step 6, what are you looping?

I tested similar code and pdf is created.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[jsdba]

Well, that is odd because the SQL shown should not open with that filter criteria. Without space, ClientID value will run into "AND" and the WHERE clause will be gibberish.

Have you step debugged?
Where does IncrementalInvoiceNum value come from? Is that a field or control name?
Why do you need to save aggregate data?
Re Step 6, what are you looping?
I tested similar code and pdf is created.
If you want to provide db for analysis, follow instructions at bottom of my post.

Debugger - I've stepped through with no luck. the code just literally stops running at docmd.Outputto. no errors.

IncrementalInvoiceNum - is a control on the report

Agg Data - While not the best practice it just makes capturing this data easier (pretty sure irrelevant to the problem)

Looping - i have a dataset with a list of clients. i'm looping through that list to create an invoice for each of them

DB - i cannot provide BD