Accessing Public Function from report field not working (1 Viewer)

Hi,

totally frustrated........

I have a report, it creates an invoice summary from a table called invoices. So, if I do an invoice run for the month of April the summary reports displays the customer name and basic details of the invoice raised. The report is based on the invoice table.

I have a field on the report where I want to display the total amount of invoices raised for each customer. I have a public function GetTotal_Invtodate and using expression builder, I make the field =gettotal_Invtodate( Parameter1, parameter2)

I been round the forums and enabled macros, set trusted locations etc....my public functions are in the Module 1...so are independent, global functions.

When I run the report, the parameter box displays with the name of the function, I just click ok as I am not sure what to put in there, the report runs but the field in question has #Error

If I go into the immediate window and type ? gettotal_invtodate(p1,p2) I get the correct answer......

I am obviously doing something wrong but for the life of me I can't understand what it is.......my patience with Access is running on fumes right now

Any help, advice, guidance much appreciated

Thanks
Lee

dont access functions from the report, instead,
do it in the query of the report.

It would help if you posted your function together with details of where the parameter data is located.

Reports are not usually interactive.
My suggestion would be to include the parameters in the filter criteria used for selecting data for the report.

EDIT just seen that ranman said much the same thing whilst I was typing my response

HI,

thanks for your replies. I am not sure I quite understand. I am not trying to make the report interactive...I don't want the parameter box to appear, if I did, then I would do what your suggesting...

What I want to do is have a single line per customer on the report. The user enters the date they want i.e. Invoice Summary for 1/4/2017 to 30/4/2017, these are referred to as Startdate and Enddate.....these fields exist on a form where the user enters the dates and clicks 'Invoice Summary'

0001: Customer Name: Inv_date, This_Inv_Total, TotalInvoiced_todate, total_Payments_todate, current_balance

I have a public function: GetTotal_Inv_Todate(p1,p2) that needs to be called to obtain the TotalInvoiced_todate figure.

P1 is the customerID obtained from the query that runs which underpins the report

p2 is the enddate

The public function should be called for each customer returned in the query. Once the user has clicked 'Invoice Summary' there is no need for any further user entry.

Hope this helps

Lee

I would handle the Format event of the report section that contains the textbox whose value you want to set. Then run the code you need to run, assigning what values you need to assign, at that time.
Keep in mind that the value of a control in one section of a report may not be available to all sections of the report at all times. A report is processed dynamically, and parts of it may be spooling off the printer while other parts are still being composed by your code. As a result, timing is crucial, and the best place to do most work is in the format event handler of the section that contains the control you want to set.
hth
Mark

Marks' comments are excellent advice

I am not sure I quite understand. I am not trying to make the report interactive...I don't want the parameter box to appear, if I did, then I would do what your suggesting...

Click to expand...

By 'interactive' I meant that you are requiring user input once a report has loaded. If you set the parameter criteria in the report data source, this wouldn't happen.

You can also get spurrious parameter popup prompts if you have SQL in combos and listboxes that rely on fields that are no longer in the report's SQL.

You have also referred to the function inconsistently in your posts...

=gettotal_invtodate(p1,p2)
GetTotal_Inv_Todate(p1,p2)

Click to expand...

...which might also cause a prompt.
Mark