Solved

Referencing a report text box from VBA code on a form

Posted on 2008-10-26
7
1,209 Views
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?
0
Comment
Question by:Brendo74
[X]
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
7 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 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.
0
 
LVL 1

Author Comment

by:Brendo74
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")
 
    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

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
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?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 22807318
Brendo74,

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.

JeffCoachman
0
 
LVL 1

Author Comment

by:Brendo74
ID: 22808892
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

Open in new window

0
 
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?

JeffCoachman
0
 
LVL 1

Accepted Solution

by:
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!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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