Need a better solution - Selective Report

[golfinholic]

Hi all,

New to here, new to access too. I only seriously develop stuff in Access since less than 2 weeks ago.

One of the report I have developed (its on live now) is PAINFULLY slow. So I want better solution with it. Its not super urgent but would be nice if I can improve it ASAP.

Requirement:
A customer requires a Access Report to print their invoice. BUT depends on the value in a piece of record, it can output 3 completely different layout. The invoices has to output in a specific order by order number etc. Both 3 type of report can use same query.

Currently:
I have created a MainReport and 3 x subReport (subrptFormat1, subrptFormat2 and subrptFormat3) inside it. Long story short the main report using a query (select * from tblInvoice order by invoiceID). The main form also has 2 x textboxes which will be popluated with value of "InvoiceID" and "Type"

3 x subreport will populate by query (select * from tblInvoice where InvoiceID = [Reports]![MainReport]![txtInvoiceID]).

At the same time, the main report will hide 2 of 3 subreports depends on the "Type" value of the record. In the VBA I do the following in the main report:

if [Type].value = 1 then
Me.subrptFormat1.Visible = true
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = false
elseif [Type].value = 2 then
Me.subrptFormat1.Visible = false
Me.subrptFormat2.Visible = true
Me.subrptFormat3.Visible = false
else
Me.subrptFormat1.Visible = false
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = true
endif

End Result:
So I have all these different format reports, consume the same query can able to keep the order I want, in ONE report window.

-----------------------------------------------------------

But as you can see its PAINFULLY slow. Generally speaking for EVERY record it needs to go thru the MainReport, then each of the subreport, then hide the other 2 subreports is not using at this particular page.

I thought there would be something I can "Programatically suppress" the 2 subreports I don't use for a particular page to run with condition on the "Type" value but I found nothing useful.

I also thought of creating just 1 x report with all the dynamic formating stuff in there (Control.Visible = true/false) but maintainence would be a nightmare PLUS adding another report format into it will become almost mission impossible.

Can anyone provide me a better solution to do what I am doing?

Thanks in advance


p.s. Some more background info:
The query can return 60,000 rows worth of records.

 

[golfinholic]

Please, anyone. Even half a clue will do.

Thanks.

 

[boblarson]

When do you know which sub report will be needed? Is it before you open the report?

 

[golfinholic]

Hmm...

I run the Query in the main report (which has nothing BUT 3 x subreports + 2 x textboxes - textbox1 holds value of "ID" and textbox2 holds value of "TYPE").

For each record it reads in, I populate the textbox ("ID", as well as the "TYPE" ) and the ALL 3 x subreports will populate with the value from the "ID" in textbox1.

Then after both 3 x subreports populated, depends on the value of "TYPE" in textbox2. I hide 2 of them and show the one I want to with code like the following:

Me.subrptFormat1.Visible = true
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = false

So to answer your question, with my current (rather lame) approach, I only know what report I need AFTER I open the report and subreports.

So what I am try to get here, is...

After A record is read into the Main report BUT before the subreport is open. How and/or Where, can I make the decision - suppress the 2 subreports I don't need and only get the one I need to populate?

Hope I have make it clearer this time?

 

[boblarson]

Well, I think your setup is not going to work.

Subreports are opened BEFORE the main report, so that kind of puts a bit of a snag into things.

 

[mlai08]

Hmm...

I run the Query in the main report (which has nothing BUT 3 x subreports + 2 x textboxes - textbox1 holds value of "ID" and textbox2 holds value of "TYPE").

For each record it reads in, I populate the textbox ("ID", as well as the "TYPE" ) and the ALL 3 x subreports will populate with the value from the "ID" in textbox1.

Then after both 3 x subreports populated, depends on the value of "TYPE" in textbox2. I hide 2 of them and show the one I want to with code like the following:

Me.subrptFormat1.Visible = true
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = false

So to answer your question, with my current (rather lame) approach, I only know what report I need AFTER I open the report and subreports.

So what I am try to get here, is...

After A record is read into the Main report BUT before the subreport is open. How and/or Where, can I make the decision - suppress the 2 subreports I don't need and only get the one I need to populate?

Hope I have make it clearer this time?

Can you not split up the report to 3 separate reports with different subreport format?

You may run the query first to determine the Type and run the appropriate report depending on its value.

If Type.value = 1 then
docmd.open report "Report 1"
elseif Type.value = 2 then
docmd.open report "Report 2"
...etc.
end if

Depending on how you want to run the main query, you may also put the query and open report command into a marco which sets off both actions in sequence.

Mike

 

[boblarson]

So are you trying to display a different subreport for EACH RECORD on the report? Or is it a change for each report?

 

[golfinholic]

I am not sure if I got your wordings right... with you question I would say I want to have a different subreport for each records.

Lets put it this way (probably easier... I guess)

Recordset has 10 records

ID TYPE
1 1
2 2
3 1
4 3
5 1
6 3
7 2
8 2
10 3

So with my current setup, I will have A report, contain 10 pages. But each pages will look different depends on the value in "TYPE field"

Do I make it easier to understand or not?

 

[golfinholic]

Can you not split up the report to 3 separate reports with different subreport format?

You may run the query first to determine the Type and run the appropriate report depending on its value.

If Type.value = 1 then
docmd.open report "Report 1"
elseif Type.value = 2 then
docmd.open report "Report 2"
...etc.
end if

Depending on how you want to run the main query, you may also put the query and open report command into a marco which sets off both actions in sequence.

Mike

No. With you method I will create multiple reports for each record, instead having all records in A report just different format.

 

[boblarson]

I think you are basically up a creek without a paddle as it will require loading all subforms and then the report.

 

[golfinholic]

So.... no hopes?

 

[boblarson]

Basically, none I can think of. If you are going to display the subreport based on record, then it will load them all up. There's not much you can do about that.

 

[khawar]

Can you upload a sample of your database there may be an alternative workout


Khawar