Runtime function for all reports.

[dave]

In a split Access database with front-end and back-end I want to run a
function each time ANY of the 110 reports is opened by the user.
IMPORTANT: I don't want to have to add to every report the code that
calls the function - there are good reasons for this - I just want the
program to know that a report is being opened - any report - by the
user and that this function should therefore be called first before the
report is opened.

Thanks in advance for any help/suggestions.

 

[Douglas J. Steele]

Sorry, but unless you limit the users to running reports from a form you've
created, you're going to have to add code to each of the reports. This
doesn't mean having to copy-and-paste code into each report, though: you can
create a common function in a module, and have each report call that
function when you open it.

 

[dave]

Thanks Doug,

Yes, I was hoping to avoid pasting a call to a function in the OnOpen
event of all 110 reports. I guess I am really looking for some global
code that will change the behaviour of all the reports in the database.

OK - here's a sub-question: is there any way of adding the Function
call to each report's OnOpen event when the database starts up e.g For
each report, insert one line of code at the head of each report's
OnOpen event bearing in mind that most of the reports already have some
code in their OnOpen events. By the way, this is not just laziness on
my part - there is a good reason why I need to do it this way.

Thanks again.

 

[Rick Brandt]

Thanks Doug,

Yes, I was hoping to avoid pasting a call to a function in the OnOpen
event of all 110 reports. I guess I am really looking for some global
code that will change the behaviour of all the reports in the
database.

OK - here's a sub-question: is there any way of adding the Function
call to each report's OnOpen event when the database starts up e.g For
each report, insert one line of code at the head of each report's
OnOpen event bearing in mind that most of the reports already have
some code in their OnOpen events. By the way, this is not just
laziness on my part - there is a good reason why I need to do it this
way.

Thanks again.

Are you opening these from typical "application methods" like buttons on forms
and such or are you wanting this behavior even when a report is just opened from
the db window? I don't think the latter is possible without modifying every
report. I suppose if this is an MDB you could modify the design of every report
on open, but I suspect that would be slow and might cause file bloat.

If you are using application methods you could create a wrapper function for
DoCmd.OpenReport that does the same thing and takes the same arguments, but has
the added feature of doing whatever it is you want in your function. Then you
could use a Find and Replace utility to replace every instance of OpenReport
with your wrapper function. I created a wrapper like this that always ignores
error 2501 so I don't have to code for that in so many individual reports.

 

[dave]

Rick,

Thank you for the suggestion in your second para. - I hadn't thought of
that. I'll try it and in the meantime it would be interesting to see
the example of your wrapper function if you have time.

 

[Douglas J. Steele]

Perhaps if you explained the "good reason why [you] need to do it this way"
we could suggest an alternative.

You can, of course, add code to modules programmatically. Here's some
untested air-code that will open every report in the application. If there
is a "Report_Open" sub already, the code will add a line "Call MyFunction()"
at the top of that sub. If there isn't a "Report_Open" sub, it will create
it, with the one line of code "Call MyFunction()" in that sub.

Sub AddCode()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim rptCurr As Report
Dim mdlCurr As Module
Dim lngSLine As Long, lngSCol As Long
Dim lngELine As Long, lngECol As Long
Dim lngReturn As Long
Dim strReportName As String
Dim strSQL As String

strSQL = "SELECT [Name] FROM MSysObjects " & _
"WHERE [Type] = -32764 ORDER BY [Name]"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do Until rsCurr.EOF
strReportName = rsCurr!Name
Debug.Print "Working on report " & strReportName
DoCmd.OpenReport strReportName, acViewDesign
Set rptCurr = Reports(strReportName)
Set mdlCurr = rptCurr.Module

' Search for string "Private Sub Report_Open"
If mdlCurr.Find("Private Sub Report_Open", _
lngSLine, lngSCol, lngELine, lngECol) Then

Debug.Print "Code added after line " & lngSLine
mdlCurr.InsertLines lngSLine + 1, "Call MyFunction()" & vbCrLf

Else

Debug.Print "Module not found: it was created."
lngReturn = mdlCurr.CreateEventProc("Open", "Report")
mdlCurr.InsertLines lngReturn + 1, "Call MyFunction()"

End If

DoCmd.Close acReport, strReportName, acSaveYes

rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

End Sub



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,

Yes, I was hoping to avoid pasting a call to a function in the OnOpen
event of all 110 reports. I guess I am really looking for some global
code that will change the behaviour of all the reports in the database.

OK - here's a sub-question: is there any way of adding the Function
call to each report's OnOpen event when the database starts up e.g For
each report, insert one line of code at the head of each report's
OnOpen event bearing in mind that most of the reports already have some
code in their OnOpen events. By the way, this is not just laziness on
my part - there is a good reason why I need to do it this way.

Thanks again.

 

[Rick Brandt]

Rick,

Thank you for the suggestion in your second para. - I hadn't thought
of that. I'll try it and in the meantime it would be interesting to
see the example of your wrapper function if you have time.

I don't have that specific example handy here at home. The basic concept though
is you create a function with an argument list that is identical to the function
you want to wrap (same arguments of the same types in the same order). Then
inside the wrapper function you call the function being wrapped and pass into it
all of the arguments that are in your wrapper function.

Just doing the above gives you a function that works exactly as the original
function being wrapped except it has a different name. Since the argument
definition is the same you know that you can use find and replace tools to
change code to use your function and it will work just as it does with the
original one.

Now, once you have a wrapper that does the exact same thing as the original you
can modify it so it has additional or different behaviors. In my case I added
error handling to the wrapper function that simply ignores error number 2501.
In your case you would add a call to your other function or you could put that
functionality directly inside the wrapper function code. Whether your additional
code is before or after the call to the function being wrapped is up to you and
depends on the specifics of what you want to do.

 

[dave]

Douglas and Rick,

Thanks so much for your help. Initially i'll try Douglas's idea; I know
it's untested but I'm sure we'll get that working as it seems to
perform exactly what we want.

Douglas, the (good) reason we don't want to hard-code this stuff into
each report very briefly is because we want to be able to create
Demonstration versions of our software for potential customers. The
software is updated fairly frequently so obviously the Demo's would
need updating too. We want to build a function into the software so
that after the full version is updated, the programmer can make a copy
of the front-end and then call this function in design-mode and quickly
convert the program into a Demo version. Amongst other things this
would add a line of code to each report that creates a Picture link for
the report to a graphic file - in effect adding a watermark to each
report saying "DEMO VERSION".

e.g .Picture = "C:\....\.....\Graphic.gif"
.PictureType = 1 '(Linked)


Thanks again.

 

[Douglas J. Steele]

Once you've got the code added to the reports in the first place, just make
sure you always add it to subsequent reports.

If you use a tool like MZ Tools (http://www.mztools.com/index.htm), it's
easy to have it automatically added for you.

 

[Rick Brandt]

Douglas and Rick,

Thanks so much for your help. Initially i'll try Douglas's idea; I
know it's untested but I'm sure we'll get that working as it seems to
perform exactly what we want.

Douglas, the (good) reason we don't want to hard-code this stuff into
each report very briefly is because we want to be able to create
Demonstration versions of our software for potential customers. The
software is updated fairly frequently so obviously the Demo's would
need updating too. We want to build a function into the software so
that after the full version is updated, the programmer can make a copy
of the front-end and then call this function in design-mode and
quickly convert the program into a Demo version. Amongst other things
this would add a line of code to each report that creates a Picture
link for the report to a graphic file - in effect adding a watermark
to each report saying "DEMO VERSION".

e.g .Picture = "C:\....\.....\Graphic.gif"
.PictureType = 1 '(Linked)

All you need is to put that code in ALL reports built but with an If-Then block
that tests for whether the app is in "demo mode". That test can easily be
driven by an entry in a local table or with a custom database property.

(simple is better than complex)

 

[dave]

Yep - more good suggestions from both of you. We've got plenty to work
on now so thanks again for all your help.