Referencing a report text box from VBA code on a form

The strange thing is that it works for the first record, but not for subsequent records when I reapply the filter.  The exact error that I get is "Run-time error #2427: You entered an expression that has no value."

The relevant code snippet is below.  In my test data there are effectively 2 report records (based on the Billing Account ID) and the first one gets created fine, but for the second time through the loop, it stops on the indicated line with the above error.  Any ideas?

Let me know if you need any more info.

    Set qdf = CurrentDb.QueryDefs("qryGeneratedBillingAccountInvoices")
    Set rst = qdf.OpenRecordset()
 
    ' Open the report initially so that we have access to it to change the filter for each billing account
    DoCmd.OpenReport "Tax Invoice", acViewReport, , , acWindowNormal
    Set taxInvReport = Reports("Tax Invoice")
 
    rst.MoveFirst
    While Not rst.EOF
        ' Set and apply the filter for the current billing account
        taxInvReport.Filter = "BillingAccountID = " & rst.Fields("BillingAccountID")
        taxInvReport.FilterOn = True
        ' Create the folder if it doesn't already exist for the billing account
        ' *** EXECUTION WILL STOP ON THIS NEXT LINE 2ND TIME THROUGH ***
        strFullPath = strCurrentPath & taxInvReport!txtAccountName & "_" & rst.Fields("BillingAccountID")
        If Len(Dir(strFullPath, vbDirectory)) = 0 Then
            MkDir strFullPath
        End If
        strFileName = "Invoice.pdf"
        DoCmd.OutputTo acOutputReport, "Tax Invoice", acFormatPDF, strFullPath & "\" & strFileName, False
        rst.MoveNext
    Wend

Select allOpen in new window

Well I'm surprised it works once, so not surprised it fails.

But given the success on the first pass, are you sure that there is data that matches the filter on the second pass?

Jeff,

Basically, the system is a deliveries and invoicing system.  Deliveries are made during the invoicing period and then at the end of that the idea is that the user goes into a form and clicks on a "Generate Invoices" button (the code above is taken from the Click event of that button) to generate individual invoices for each of the customers.  The invoice report that I've developed generates invoices for all customers, hence the loop and the application of a filter each time (on BillingAccountID).  This report takes its data from a query (the record source is already set up in the report itself).

My idea was to loop through and generate the report each time for each different BillingAccountID and then grab information from the report to populate in the file name and folder name, as well as adding a row to an Invoice table to store the relevant totals from the report (the code for that hasn't yet been incorporated).  The reason why I wanted to go to the report to get the values was to hopefully speed up processing a bit, since it is quite a complex query that populates the report and I will need to generate approximately 200-300 invoices each invoice period (I didn't want to be re-running the query unnecessarily).

So to summarise, I am trying to put values/calculations from the report to a table, so from your comment, this is a bad idea.  I did get it to produce the results that I need (I've pasted the entire method block below), but if you could advise if there is a "best-practice" approach to this situation, that would be fantastic.

Cheers,

Brenden

Private Sub cmdGenerateInvoices_Click()
 
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef
    Dim strPathComponents() As String
    Dim strCurrentPath As String
    Dim i As Integer
    Dim tempVal As Integer
    Dim strFullPath As String
    Dim strFileName As String
 
    ' TODO: Add code to ensure that invoices for the selected period have not yet been generated
    ' TODO: Discover why the next null check isn't working and fix it
    If Me![Next Invoice Period]!cboInvoicePeriod = Null Then
        MsgBox "Please select an invoice period.", vbOKOnly + vbExclamation, "Preview Invoices"
    Else
        ' Set parameters used to populate the report.
        ' These parameters are used by the query in the recordsource
        ' of the report.
        tempVal = Me![Next Invoice Period]!cboInvoicePeriod
        modParams.SelectedInvoicePeriod = Me![Next Invoice Period]!cboInvoicePeriod
        modParams.HeaderLine1 = Me.Invoice_Header_Info!HeaderLine1
        modParams.HeaderLine2 = Me.Invoice_Header_Info!HeaderLine2
        modParams.HeaderLine3 = Me.Invoice_Header_Info!HeaderLine3
        modParams.HeaderLine4 = Me.Invoice_Header_Info!HeaderLine4
        modParams.InvStartDate = Me![Next Invoice Period]!txtPeriodFrom
        modParams.InvEndDate = Me![Next Invoice Period]!txtPeriodTo
        Me![Next Invoice Period]!cboInvoicePeriod = tempVal
    End If
 
    strCurrentPath = ""
    strPathComponents = Split(CurrentDb.Name, "\")
    For i = 0 To UBound(strPathComponents) - 1 ' We just want the base path, not the file name
        strCurrentPath = strCurrentPath & strPathComponents(i) & "\"
    Next i
    strCurrentPath = strCurrentPath & "Invoices\"
    Set qdf = CurrentDb.QueryDefs("qryGeneratedBillingAccountInvoices")
    ' Note that qdf is not the entire query that is used to populate
    ' the report - it only returns the unique list of BillingAccountIDs
    ' The full query is set in the recordsource of the report itself.
    Set rst = qdf.OpenRecordset()
 
    rst.MoveFirst
    While Not rst.EOF
        DoCmd.OpenReport "Tax Invoice", acViewReport, , "BillingAccountID = " & rst.Fields("BillingAccountID"), acHidden
        ' Create the folder if it doesn't already exist for the billing account
        strFullPath = strCurrentPath & Reports("Tax Invoice")!txtAccountName & "_" & rst.Fields("BillingAccountID")
        If Len(Dir(strFullPath, vbDirectory)) = 0 Then
            MkDir strFullPath
        End If
        strFileName = Reports("Tax Invoice")!txtInvoiceReference & ".pdf"
        DoCmd.OutputTo acOutputReport, "Tax Invoice", acFormatPDF, strFullPath & "\" & strFileName, False
        DoCmd.Close acReport, "Tax Invoice", acSaveNo
        rst.MoveNext
    Wend
 
    ' TODO: Add code to insert the invoice records into the Invoice table
    ' TODO: Add code to advance the next invoice date in the InvoicePeriod table for the selected invoice period
 
    MsgBox "Invoices successfully generated.", vbOKOnly + vbInformation, "Generate Invoices"
 
End Sub

Select allOpen in new window

To insert a value into a table is pretty straight forward.

Do the corresponding records exist in the target table already?
Or is the insert actually going to create a new record?

JeffCoachman