Looking for Access Reporting Add-ins

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Alston - 11 Dec 2005 04:18 GMT

I am looking for Access reporting add-in that would be easy to use by
end users. My key focus is on selection criteria. I am very happy with
the Access report writer capabilities. As far as development of
reports, it is certainly fine in my book.

But for end-users, with little experience or training, it would be nice
to have an easy way to handle various selection criteria, perhaps with
relatively stock reports. I see easy to use by novice end-users of
selection capabilities as the biggest limitation in just directly having
users use the Report Wizard!

I certainly have built forms with selection criteria on the form that is
then applied to the reports. This works well for reports where the
selection criteria applies to all reports. but if the reports are
varied, each with various potential selection, such would mean unique
selection criteria forms for each report or report type - seems like a
lot of work.

So far I have found the following:

Cub Reporter for MS Access $40
--------------------------
https://www.swreg.org/soft_shop/224/shopscr7.shtml

This is an add in you can add to existing reports. Pretty nice
capabilities for selection, based on the fields in the report
definition. Apparently can be easily added on to previously prepared
reports.

Might even be able to have users create the basic report using the
report wizard. then teach how to add the link to this add-in in the
report. When added, it would give easy to use selection criteria - I think.

Output Writer Wizard for MS Access $50
----------------------------------
http://www.acc-technology.com/owwiz.htm
Advantage is that it allows you to select the fields for the report and
build it all in one. Only three selection criteria. No totals. Stand
along capability - not used with predefined reports. Seems very basic
in capabilities.

Drill down Tally 2005 - $200
---------------------
http://www.intellirel.com/DrillDownTally.asp
Seems to be more oriented to drill down capabilities.
Haven't looked very far into this.

ASP Report Wizard 2.0 - free for educational - $395 commercial
---------------------
http://www.aspwebsolution.com/products/report%5Fwizard/index.htm
Not really an Access add-on. A web based reporting capability.
Looks interesting but not investigated very far.

I would be VERY interested in suggestions for other products you may
have used with success, experience you may have in trying or using any
of the above products, or alternative suggestions for providing easy to
use selection with Access built in report capabilities including the
report wizard.

Bob

bobalston9 AT yahoo DOT com

Reply to this Message

Larry Linson - 11 Dec 2005 06:24 GMT

Bob,

I don't know of Add-Ins, but have worked on projects where there was a form
for selection that applied to many different reports -- of course, they all
dealt with much the same data. I am sure it would be possible to write a
more general one, picking up fields from the RecordSource of the selected
Report. The problem I see with that is that it'll be relatively easy to do
for reports that do have much the same data, because you'll know in advance
whether your selections have to be direct entry into a text box, or whether
you can let the user select in a combo box, use a date picker, and/or enter
a range or a before or after date option.

On that same project, the client project administrator had been sold on a
feature of another product... a very simple report generator, allowing the
user to pick from a predefined set of fields, specify some selection, etc.
But, in making it what they thought was simple enough for an average user,
they made it too simple for the data to be useful to any of the users. They
put a good deal of time and effort into it, and it ended up never being used
(as far as we could determine) beyond a little experimentation right after
it was introduced to the users. So, you have to guard against
over-simplification.

Larry Linson
Microsoft Access MVP

>I am looking for Access reporting add-in that would be easy to use by end
>users. My key focus is on selection criteria. I am very happy with the
[quoted text clipped - 56 lines]
>
> bobalston9 AT yahoo DOT com

Reply to this Message

Bob Alston - 11 Dec 2005 12:15 GMT

> Bob,
>
[quoted text clipped - 81 lines]
>>
>>bobalston9 AT yahoo DOT com

Thanks Larry.

The first product in my original message, Cub reporter, seems like a
nice, simple add-in to Access reports that would provide user-defined
data selection. And it can support fairly complex selections by the
option of entering a where clause. And it would seem to avoid a lot of
development by me.

Now a tough question. To make this add in easily available to user
generated reports, generated using the Report Wizard, it would be nice
if I could use VBA code to automatically add the online OPEN EVENT to
the report. I could have detailed instructions for the user to edit the
report design and add the event but that seems cumbersome. I would plan
to have a button to bring up a form where the user selected a report
from a drop-down list and then selected a button to run the code to add
the open event to the selected report.

I am experienced in writing Form events and with writing VBA code to
access table and query collections. But I cannot find anything relative
to how I could use VBA code to create an Open event for a report.

Suggestions?

Bob

Reply to this Message

Bob Alston - 11 Dec 2005 13:31 GMT

>> Bob,
>>
[quoted text clipped - 111 lines]
>
> Bob

I think I am slooooowwwly getting there. I believe I can refer to the
report object (not open) via this code:

Set rptcurr = CurrentProject.AllReports("test")

Still stumped to get access to the module object that is part of the
report object.

I also understand that there is one module in the report object and you
can get various properties to parse the various procedures (functions,
subprograms, etc) within it. If I am creating the only procedure in the
module, that might be pretty easy.

Bob

Bob

Reply to this Message

Bob Alston - 11 Dec 2005 19:02 GMT

>>> Bob,
>>>
[quoted text clipped - 129 lines]
>
> Bob

OK. I have the following code which creates the procedure in the report
module. But I end up with the VBA window open for the report module and
the report is still open in design mode. How do I automatically close them?

Function createrptmodule()

Dim strcode As String
Dim modulecur As Module
Dim rptcur As Report

DoCmd.OpenReport "test", acDesign, , , acHidden
Set rptcur = Reports![test]
Set modulecur = Reports![test].Module
Dim lngreturn As Long
lngreturn = modulecur.CreateEventProc("open", "report")
strcode = "' call whatever"
modulecur.InsertLines lngreturn + 1, strcode
Set modulecur = Nothing
DoCmd.Close acReport, "test", acSaveYes
Set rptcur = Nothing

End Function

Reply to this Message

Lyle Fairfield - 11 Dec 2005 22:11 GMT

When I copy your code, and make a copy a report of mine and call that
copy "test", your code works swimmingly if I add this line:

Set rptcur = Nothing
*****
Application.VBE.MainWindow.Visible = False
*****
End Function

I haven't checked this a lot but I haven't noticed any bad things
happening after running it a few times.

Are you sure the Report is still open? If you've closed it and not
received an error message then it must be closed?

I think the line of code I posted will not work in Access less than
2000.

Reply to this Message

Bob Alston - 11 Dec 2005 23:25 GMT

> When I copy your code, and make a copy a report of mine and call that
> copy "test", your code works swimmingly if I add this line:
[quoted text clipped - 13 lines]
> I think the line of code I posted will not work in Access less than
> 2000.

Thanks. I also found that I needed to add that line to close the VBA
window.

I also added some logic to check for a report without any modules and to
set hasmodules=yes. That should be necessary in the situation I
anticipate. My final code is:

Function CreateRptModule()
Dim strcode As String
Dim modulecur As Module
Dim rptcur As Report
Dim lngreturn As Long
DoCmd.OpenReport "test", acDesign, , , acHidden
Set rptcur = Reports![test]
If Not rptcur.HasModule Then
rptcur.HasModule = True
Set modulecur = Reports![test].Module
lngreturn = modulecur.CreateEventProc("open", "report")
strcode = "' call whatever"
modulecur.InsertLines lngreturn + 1, strcode
End If
DoCmd.Close acModule, "Report_test", acSaveYes
DoCmd.Close acModule, "module2", acSaveYes
Set modulecur = Nothing
DoCmd.Close acReport, "test", acSaveYes
Set rptcur = Nothing
' close visual basic window
Application.VBE.MainWindow.Visible = False

End Function

Reply to this Message