Run Reports using VBA or Local table

[BenMason]

The Access database has a SQL backend with tables, views, SP's.

I have reports to run based on rptArea and I am currently using vba but wondering if it's better to use a report table. For instance, sample vba code:

if rptArea is "Mktg" then
Case M1
rptName = "Mktg1_rpt"
strdisplayName = "MKtg1"
Docmd.OpenReport "Mktg1_rpt", acViewPreview
Reports(strReportName).Caption = strdisplayName
Case M2
.... same thing but different report
Case M3
.... same thing but different report
End Select

if rptArea is "Finance" then
Case F1
rptName = "Finance1_rpt"
strdisplayName = "Finance1"
Docmd.OpenReport "Finance1_rpt", acViewPreview
Reports(strReportName).Caption = strdisplayName
Case F2
.... same thing but different report
Case F3
.... same thing but different report
End Select

There are 12-15 rptArea .

I can do this in VBA, but was wondering if a table would be better. Then if they add a new report or delete a report, it can be done in the table.
But I don't know how to populate and run the reports from a table with the criteria from above.

Maybe TABLE:
the table would have fields:
rptArea, rptName , strdisplayName , Reports(strReportName).Caption, Docmd.OpenReport "Finance1_rpt", acViewPreview

Then the VBA would only be:
if rptArea is "Mktg" then
DLookup the table that has the criteria from above, but it would need to lookup each report, and there can be 1 or many reports per rptArea. How to loop through the table and run each report? Not sure. Looking for a way to streamline the VBA used to run the reports.

I also thought about putting the report names and other criteria in a view or SP and have it loop through there. But need help.

Any ideas, suggestions is appreciated?

Thanks,
Ben

 

[Gasman]

Use a recordset for reading which reports to run.
That vba would not run as well.

 

[DHookom]

I would consider using a combo box based on your table of reports and attributes. Then you could easily reference the columns of the combo box for the setting properties.

 

[BenMason]

I found something for recordset. I can still set up a local table, right, and just use the one recordset routine?? Here's what I have in draft version:
if rptArea is "Mktg" then
Call GenerateReportsByrptArea
End if

Sub GenerateReportsByrptArear()

Dim rs As DAO.Recordset
Dim reportCriteria As String

' Open a recordset of customers who need reports
Set rs = CurrentDb.OpenRecordset("SELECT rptArea, displayname, reportname.caption FROM ReportTable WHERE rptArea = Mktg")

With rs
Do While Not .EOF
' Get the rptArea for the current record
reportCriteria = "[rptArea] = " & rs![CustomerID]

' Open the report filtered by the current CustomerID
DoCmd.OpenReport "Mktg1_rpt", acViewPreview

.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

End Sub

'============
The above is from a sample recordset , I need to fill in. But is this what you mean? Just one DAO for all the reports and just loop thru the table based on if the rptArea?

Still a little vague on how to do this. Please help steer me in the right direction.

Thanks,
Ben

 

[BenMason]

I would consider using a combo box based on your table of reports and attributes. Then you could easily reference the columns of the combo box for the setting properties.

humm... The client wants a button to click on. Then the code would do the processing. I have the VBA, but I think there is a better way to streamline the code. But need help.

Thanks,
Ben

 

[plog]

Maybe TABLE:
the table would have fields:
rptArea, rptName , strdisplayName , Reports(strReportName).Caption, Docmd.OpenReport "Finance1_rpt", acViewPreview

I don't understand why you need all those fields. Doesn't the report itself have all those values in it? Why does it need to be taken from somewhere?

Seems like you just need to associate rptArea with an actual report to open.

How many reports do you have? Looking over your first post, I'm confused about how rptArea fits into this. You have report areas but then under those you have different reports?

How many finance reports do you have and what makes them different? Do they have the exact same layout but different criteria?

 

[Gasman]

I would consider using a combo box based on your table of reports and attributes. Then you could easily reference the columns of the combo box for the setting properties.

Better idea. Did it myself a good while back, which is why I did not think of it now.

However I was only running one or two different reports at a time.

 

[BenMason]

Maybe TABLE:
the table would have fields:
rptArea, rptArea , rptArea , Reports(strReportName).Caption, Docmd.OpenReport "Finance1_rpt", acViewPreview

I don't understand why you need all those fields. Doesn't the report itself have all those values in it? Why does it need to be taken from somewhere?

Seems like you just need to associate rptArea with an actual report to open.

How many reports do you have? Looking over your first post, I'm confused about how rptArea fits into this. You have report areas but then under those you have different reports?

How many finance reports do you have and what makes them different? Do they have the exact same layout but different criteria?

I don't know for sure, and maybe I'm stuck with just using VBA. But I was wondering if a table would work, like this. there are 12- 15 rptArea and each area can have 1 or more reports

rptArea	rptName	strdisplayName	strReportName	Execute Report Code
Mktg	Mktg1_rpt	MKtg1	strdisplayName	Docmd.OpenReport "Finance1_rpt", acViewPreview
Finance	Finance1_rpt	Finance1	Finance1_rpt	Docmd.OpenReport "Finance1_rpt", acViewPreview

if something like this doesn't work, please let me know.
Ben

 

[Gasman]

humm... The client wants a button to click on. Then the code would do the processing. I have the VBA, but I think there is a better way to streamline the code. But need help.

Thanks,
Ben

So use a combo to select and a button to run whatever sequence is required.

 

[Gasman]

I don't know for sure, and maybe I'm stuck with just using VBA. But I was wondering if a table would work, like this. there are 12- 15 rptArea and each area can have 1 or more reports

rptArea	rptName	strdisplayName	strReportName	Execute Report Code
Mktg	Mktg1_rpt	MKtg1	strdisplayName	Docmd.OpenReport "Finance1_rpt", acViewPreview
Finance	Finance1_rpt	Finance1	Finance1_rpt	Docmd.OpenReport "Finance1_rpt", acViewPreview

if something like this doesn't work, please let me know.
Ben

You do not need strReportName, you already have it.
You were not even setting it in the vba anyway.
Not sure how you can run a field value? Not in Access anyway? Just have a ReportView field.

 

[BenMason]

You do not need strReportName, you already have it.
You were not even setting it in the vba anyway.
Not sure how you can run a field value? Not in Access anyway? Just have a ReportView field.

Okay, seems like the table will not work. I was hoping to use it to pass the table values as parameters and have it loop through the table parameters and run the report based on the parameters. But, sounds like VBA is the best structure. If I find out something different, I'll post it here.

Thanks, Ben

 

[Gasman]

Okay, seems like the table will not work. I was hoping to use it to pass the table values as parameters and have it loop through the table parameters and run the report based on the parameters. But, sounds like VBA is the best structure. If I find out something different, I'll post it here.

Thanks, Ben

Wrong. Whatever you pass to the OpenReport command can come from table fields. Criteria, View, OpenArgs the lot.

 

[plog]

rptArea	rptName	strdisplayName	strReportName	Execute Report Code
Mktg	Mktg1_rpt	MKtg1	strdisplayName	Docmd.OpenReport "Finance1_rpt", acViewPreview
Finance	Finance1_rpt	Finance1	Finance1_rpt	Docmd.OpenReport "Finance1_rpt", acViewPreview

I need better specifics to help you. My gut tells me a table might be the way to go after some revisions to your reports. But what you've given and explained isn't helpful.

Since you haven't given me much to go on, here's my unspecific thoughts:

1. Your reports can be consolidated. I bet you can knock your reports down to 3 or 4 base reports. If all your reports have the exact format and layout and only differ in the criteria of the data they display--you don't need multiple reports. You can make 3 or 4 base reports and then use the criteria element of DoCmd.OpenReport to customize the reports upon open.

2. Once consolidated you can use a table to store all the information to turn your base reports into the existing 12-15 reports you currently have. You'd store the title, the name and the criteria to make it in a table and then use VBA to customize the report when it opens. That gives the appearance of 12-15 reports but using only 3-4 actual report objects.

3. Again, I'd need specifics of your database to give you specifics to achieve this. Can you upload a sample of it?

 

[Pat Hartman]

Here are two variations that you might be able to adapt to your needs.
This first one is probably the simplest to adapt. You could add a combo to filter the list box. But the second option runs all selected reports as a batch.

Manage Reports form

This sample just can't be imported and used, it requires customization. There is a table behind the scenes which documents the reports and also identifies each report's arguments and whether they are required or optional. I've used this concept in at least a dozen applications and given that...

www.access-programmers.co.uk