Report issue - it works unreliably (1 Viewer)

[amarton]

Hi All,

Currently, I'm working on a little project for a friend, where I needed to upgrade his db. It works great, but at the end of the month, based on the details, I would like to generate a PDF invoice for the customers based on their monthly activities.
Created the query, which works fine and created the Invoice format. Both is using a unique ID for filtering the activities, which need to be invoiced.
I added a button to the customer menu, which uses the same unique ID to show the billable activities.

The problem is that the button works only once. It shows me the report format, with the right details. I can export it into PDF, but after I close the db, and reopen, after pressing the button, it asks for the unique ID and the date range. Going into the underlying query, changing one character on the sorting, removing the character and saving, it works again, but not after closing the db.

Where and what I'm doing wrong?

 

[MajP]

That is a hard problem to find based on generalities. The best would be to post the DB. If the data is sensitive you can scramble it or provide a few fake records. If that is too hard create a new db and import the report. Then put some blank tables in it. At a minimum post the reports code or any code to launch the report.

 

[Ranman256]

usu if it asks for parameters, then a form or query has a criteria box waiting to be filled, but the form or source query is no longer open.,

 

[Pat Hartman]

This has nothing to do with the problem at hand. We need the database to help you with that --

Having to call up individual customer records, one by one, every month, to produce an invoice for the month is very cumbersome. A better solution is to create a query that selects a list of customers which have had activity during the month and so which need an invoice. Then your code would loop through the selected records and inside the loop generate an invoice and save it to PDF for each customer. Code samples abound if you need one.

 

[amarton]

That is a hard problem to find based on generalities. The best would be to post the DB. If the data is sensitive you can scramble it or provide a few fake records. If that is too hard create a new db and import the report. Then put some blank tables in it. At a minimum post the reports code or any code to launch the report.

Thanks, I will prep the db later today and share it.

 

[amarton]

This has nothing to do with the problem at hand. We need the database to help you with that --

Having to call up individual customer records, one by one, every month, to produce an invoice for the month is very cumbersome. A better solution is to create a query that selects a list of customers which have had activity during the month and so which need an invoice. Then your code would loop through the selected records and inside the loop generate an invoice and save it to PDF for each customer. Code samples abound if you need one.

The way I did it is the following:
- created a site form, with all the details
- created a subform, which collects and displays the billable activities
- added a time filter on the form to select the date range
- created a query, which is using the SITEID and the date range filter from the site form
- added a button to run the query and show a report (it just lists the billable activities in a printable form)
- created another query, which collects all the details from the customer form, what needs to be on the invoice, base on the SITEID and the date range from the forms
- added a button to run the query and show the invoice report

 

[Pat Hartman]

Still sounds like you are making the user touch each customer record to generate an invoice. Generating invoices should be automatic. This is what we call a batch process. Large companies do batch billing every day. Each day, they create the invoices for one day and send them out. This spreads the workload out over the month instead of doing a couple million invoices on ONE day, they do a few hundred thousand each day. Do you think Mastercard has a minion sitting at a computer calling up each customer one at a time to generate an invoice?

 

[amarton]

Still sounds like you are making the user touch each customer record to generate an invoice. Generating invoices should be automatic. This is what we call a batch process. Large companies do batch billing every day. Each day, they create the invoices for one day and send them out. This spreads the workload out over the month instead of doing a couple million invoices on ONE day, they do a few hundred thousand each day. Do you think Mastercard has a minion sitting at a computer calling up each customer one at a time to generate an invoice?

You are absolutely right, but they are not there yet. The project started to replace the manual excel invoicing process with some automation, which is partially working.

 

[Pat Hartman]

but they are not there yet.

You mean the application is not there yet You are spending a lot of time to produce poor functionality. Why not back up and try to do what I suggested?

 

[amarton]

You mean the application is not there yet You are spending a lot of time to produce poor functionality. Why not back up and try to do what I suggested?

You are right. What would be the best code to do anything like this?
My problem currently is that the invoice report is not filtering for the customer and the date together. It only does one or the other, but no idea why.

 

[Pat Hartman]

Start by fixing the report. Are you using a where clause in the query or are you using a where argument in the OpenReport method?

 

[mike60smart]

You are right. What would be the best code to do anything like this?
My problem currently is that the invoice report is not filtering for the customer and the date together. It only does one or the other, but no idea why.

Can you upload a zipped copy of the database?

 

[amarton]

issue is resolve, partially. as I find out, the "Invoice" is not the invoice, so they don't need to be generated, just needs to be viewable. Instead of this, I created an Invoice export, what goes in to XERO, where the invoice is generated and distributed automatically.
Thanks for you help.