Solved

Referencing a report text box from VBA code on a form

Posted on 2008-10-26
7
1,199 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
  • 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

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)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now