Convert Subprocedure into Public Module

  • Thread starter Thread starter Tom \T\
  • Start date Start date
T

Tom \T\

I know this is probably a simple task but I have 6 reports with the same
code in each of them:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const vbLightGrey = 14540253
If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey
Else
Me.Section(acDetail).BackColor = vbWhite
End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
msgbox "No data found for this Report! Please check your Date Range."
End Sub

I would like to consolidate the code and make my database more compact. Can
I put both of these Subprocedures into their own Public modules for example
modReportGreenBar and modReportNoData. If it can be done... How?

Thanks for any help in advance.

P.S.: Happy New Year

Tom "T"
 
For the first, you can put the code in a standard module, but you won't be
able to use the "Me" shortcut, so you'll have to call it and pass a "Me"
object as an argument.

The second is so short that you'd see no performance or space gain, but if
you ever have to modify the code, you'd only have to do so in one place.

They can both be in the same standard module. A module can contain multiple
procedures.

Larry Linson
Microsoft Access MVP
 
Larry,

First, Thank You for replying.

Second, do I need to declare the procedures as Public versus Private e.g.:
Public Sub ReportNoData(Cancel As Integer)?

Third, how do I call the Subprocedure from the Report's Detail Section
OnFormat Event and pass a "Me" object as an argument?

- Tom "T"
 
Something like this:

Sub DoGreyBar(rpt As Report)

Const vbLightGrey = 14540253
If rpt.CurrentRecord Mod 2 = 0 Then
rpt.Section(acDetail).BackColor = vbLightGrey
Else
rpt.Section(acDetail).BackColor = vbWhite
End If
End Sub

You would then call it from your Detail_Format event like this:

DoGreyBar Me
 
Paul,

Thank you for your help. I did just as you said, however, when I opened the
report nothing happened. Here is the code I put in modDBUtilities:

Sub DetailFormatGrayBar(rpt As Report, Cancel As Integer, FormatCount As
Integer)
Const vbLightGrey = 14540253
If rpt.CurrentRecord Mod 2 = 0 Then
rpt.Section(acDetail).BackColor = vbLightGrey
Else
rpt.Section(acDetail).BackColor = vbWhite
End If
End Sub

And here is the code I put in the Detail Section Properties... Event Tab...
On Format Event...:

DetailFormatGrayBar Me

Do I still Need the "Cancel as Integer" and "FormatCount as Integer". I got
the code from an Access web site and I am not sure what those parameters
accomplish. Can you elaborate.

- Tom "T"
 
Nothing happened? I'd be surprised if your code compiled. If you followed
my example you should have been fine. There is no need to have Cancel or
FormatCount as parameters since the routine doesn't even use them...you are
not passing them from On Format, so, your code won't run.
 
Paul,

I am sorry I should have been more specific when I said nothing happens. Let
me be more specific. When I click the button that opens the report it looks
like the screen blinks or flashes very quickly like it tried to open the
report but then closes it.

The code does compile with no errors without the Cancel or FormatCount as
arguments. I know the code works in the reports On Format Event for all 6
reports I just can't get it to work from a Module.

- Tom "T"
 
Back
Top