Referencing a report text box from VBA code on a form

Posted on 2008-10-26
Medium Priority
Last Modified: 2013-11-28
I have a form that, when a button is clicked, runs code that opens a report and then loops through each record (setting a filter each time on the report) and generates a PDF file from the filtered report for each record.  For each record, I need to insert some values from the report into another table.

Whenever I try to reference any of the text boxes on the report, I get an error with text similar to "You are referencing a property that has no value."  Can anyone tell me how I can access the report text boxes?
Question by:Brendo74
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 77

Assisted Solution

peter57r earned 1000 total points
ID: 22806431
You can only refer to report controls from within the report.
You need to put your code for updating your table into your report, if you think that is the best way to do it.

I would generally use a parameterised append query for such a task.

Author Comment

ID: 22806718
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")
    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
        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

Open in new window

LVL 77

Expert Comment

ID: 22807274
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?
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 22807318

My questions:

- Why do you need to do this?
99.5% of the time, the table/query feeds the report, not the other way around.
(Perhaps ths is why it is not working for you?)

- How did the data get into the report without being in a table/query to begin with?

- Are you trying to store a calculation on a Report to the table?
If so, this is a bad Idea.

- When you pate code here, please post the *entire* code.
For example: it is impossible to tell if you properly declared qdf, rst or strFullPath

- I really don't understand what the code is doing, or why it is doing it?

Can you simply explain what your ultimate goal is with this system?

Perhaps there is a better/simpler way.


Author Comment

ID: 22808892

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.



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"
        ' 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()
    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
    ' 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

Open in new window

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22810376
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?


Accepted Solution

Brendo74 earned 0 total points
ID: 22956423
Apologies for the delay in responding - I've been on holidays for a couple of weeks and without reliable Internet access.

The solution I went with was as follows;

* Leave the report and report recordsource query as-is to generate the individual PDF invoices to be emailed to the customers,
* Update the qryGeneratedBillingAccountInvoices summary query to grab the totals and other fields required to populate the Invoice table for each BillingAccountID.  The Invoice table stores this summary information for later use in calculations and auditing.
* Looping through each record retrieved from the qryGeneratedBillingAccountInvoices summary query; set the report filter to the BillingAccountID of the current record, use the DoCmd.OutputTo command to generate the PDF invoice to be emailed and use the DoCmd.RunSQL command to insert the summary information for the current record into the Invoice table.

So the answer to the original question of whether details from a report text box can be accessed from VBA code is yes, but there are issues and it should be avoided.  The best reason that I can come up with as to why I get errors the second time through is that Access must be doing something tricky with the object references when it regenerates the report with the new filter.  When I try to reference the same text box the second time through, it must be holding a reference to the previous instance of the text box object.  At least, the error message reads like a reworded "Object variable or with block variable not set" error anyway.  That is only my best guess, it's not the definite reason, but I think I'll know to avoid the approach of referencing anything from report text boxes in the future.

Thanks for your help - this one turned out to be a bit of a doozy!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question