count in page footer - question on FAQ: 703-3069 1

[y2k1981]

Hi All,

Just found FAQ703-3069 and it works great for me. Only problem is, I have over 100 reports that all require the exact same thing. I'm not very familiar with modules, but I figured if I just placed all the code in a module I could call that on the onOpen event instead. But when I go back in to the report properties, I see that you can only specify macros there or else enter code using the code builder. so I thought maybe if i wrote a macro that said openmodule and use that instead, but that didn't work either. so, is there any way that I can have just one module that can be used for all the reports rather than having to copy and paste it 100 times?

Thanks in advance

 

[TonyJollans]

Hi y2k1981,

You want to use RunCode in your macro. This will run the code instead of opening the editor. Note that you must run a Function (not a Sub), even if all that function does is call a Sub.

ENjoy,
Tony

 

[y2k1981]

thanks Tony, can you help me out again? How do I call a sub in a function? I'm not very VB proficient, so I need some help with this.

Thanks again
Martin

 

[TonyJollans]

Hi Martin,

Call it by name

At the simplest level, this will work ..

Function myFunction()
    mySub
End Function

Sub mySub()
    MsgBox "This is a demo"

' Your code here

End Sub

Enjoy,
Tony

 

[y2k1981]

well who would have thought !! Thanks Tony. I have one other problem, if you don't mind. this is what I have so far in the module

Public intPageCount As Integer




Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
intPageCount = intPageCount + 1
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
txtPageCount = "Number of Claims on this page: " & intPageCount
intPageCount = 0
End Sub

Private Sub Report_Open(Cancel As Integer)
intPageCount = 0
End Sub

Function addPageCount()

    Report_Open
    Detail_Print
    PageFooterSection_Format
       
End Function

as you can see the , I have the function at the end and I've managed to get the macro to call it. However, the private sub's all have arguments (ie, they have "stuff" between the brackets) but if I put this in, eg Report_Open(Canel As Integer, FormatCount As Integer) I get an error saying expected: list seperator or ). If I just put in (), it says expected =.

One other question, I have some Class Objects(eg Report_report name) which don't have anything in them (they were added when I was experimenting with something before). how do I remove them? The remove option is disabled when I right click.

Thanks for all your help Tony, hopefully you can help me out this once more?
Thanks again
Martin

 

[TonyJollans]

Hi Martin,

The routines you have there are generated event routines. When they are invoked by Access on their respective events they are passed those parameters. You CAN call them with any dummy parameters you like and the code you have will work, e.g.

Detail_Print 1,1

A much better way of doing things, however, is to have your code in your own sub (without any parameters), e.g.

Sub My_Detail_Print()
intPageCount = intPageCount + 1
End Sub

Now, you probably have those routines because you had Event Procedures set to run on Events which you have now replaced with Macros. If that is the case then you can simply replace what is there with your own routines. If, however, those routines might still be invoked on events then they will have to stay as is and, if you have code called from more than one place, it would be best to have a (further) separate routine, like this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

MyNewRoutine

' any other code

End Sub

:
:

Function addPageCount()

MyNewRoutine

' rest of your code

End Function

:
:

Private Sub MyNewRoutine()
    intPageCount = intPageCount + 1
End Sub

I hope all that makes sense.

Finally, your Report Class Modules. If there is any code behind a report it is in a Class Module attached to that report - this is created automatically for you if you set any Events to run Procedures. If you eventually don't use that code, you are left with a module - even if you delete all the code in it. It belongs to the Report and, from Access 2K onwards you can see it in the VBA editor Explorer window. To remove it you must set the Report Property HasModule to "No" - you can find this under the Other tab in the Report's Properties box.

Enjoy,
Tony

 

[y2k1981]

Hi Tony,

Thanks for your response. Boy am I confused !! Yes, you're absolutely right, they are invoked on events, but when you say " ... which you have now replaced with macros" - do you mean VBA functions, or those actual macros in Access(where you select the action from the list)?

so are you saying that they have to stay where they are, in the report class module because they're invoked as event handlers? So, because each of these is contained in the report own class module, will I just have to copy them all out to their individual report class objects, or ? sorry, I'm not very proficient with VBA, and I really do appreciate your help.

 

[TonyJollans]

Hi y2k1981,

Sorry for the delay in replying.

If we're not careful, we're going to completely confuse each other, so I'll try and go through it all step be step - I hope you'll excuse me if I oversimplify some of it.

First of all terminology is confusing. A macro elsewhere in Office tends to mean a VBA code routine. In Access a macro is something completely different and I will try and consistently use the term procedure for VBA code.

[ul][li]You can invoke either a macro or a procedure on an event.[/li]
[li]If you invoke a macro it can be any existing macro you have and many reports can invoke the same macro, and multiple events within a single report can use the same macro; there isn't any real connection between the macro and the report.[/li]
[li]If you invoke a procedure, it must be a specific named procedure in the report's code module[/li]
[li]Both macros and procedures can call other macros and/or procedures which don't have to be anywhere specific - they can be in a general code module. However, there is a slight restriction in that VBA procedures called from macros must be Functions and not Subs.[/li][/ul]
So, if you have any reports calling event procedures these need to be in the Report's code module. But if you call macros on events, these macros can call VBA procedures in any standalone module, so you only need to code once. To reiterate, I think what you're trying to do is run the same code from multiple reports; to do that you must invoke a common macro on your event and have the macro invoke a common procedure. If you want to co-ordinate across several events, have them each invoke a macro (which in turn invokes a procedure) and have all your procedures together in a single code module.

I hope that helps. if not, do come back.

Enjoy,
Tony