By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

How to pass a subreport field to a standard module function?

beacon
100+
P: 579
Hi everybody,

[Access 2010 .mdb]

I'm working with a report and I'm trying to create a standard module function that will reduce the amount of code typed into expressions on the report (since the expressions are essentially the same, save for one piece of info).

On the report, there are 11 text boxes that are identical that I've named Col1, Col2, Col3, etc. These text boxes have the following control source, where the value in the square brackets is a field that is bound to the record source for the report:

Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNothing([ABC]),0,[ABC])
  2.  
I have another 11 text boxes that are identical that I've named PercentABC, PercentDEF, etc. These textboxes HAD the following control source:

Expand|Select|Wrap|Line Numbers
  1. //This is basically an expression that checks for a value
  2. //or rounds to a certain number of decimal places.
  3. =IIf(([Col1]=0) And ([srptTimelinessTotalsByProgram].[Report]![ABC]=0 Or 
  4. IsNothing([srptTimelinessTotalsByProgram].[Report]![ABC])),0,
  5. IIf(([Col2]/[srptTimelinessTotalsByProgram].[Report]![ABC]>0.00000001) And 
  6. ([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]<0.05),([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC])+0.005,
  7. [Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]))
  8.  
I wanted to create a standard module function to reduce to cut the above code down to a single line. The function would pass in the [Col1] value and the [srptTimelinessTotalsByProgram].[Report]![ABC] to the function, and the function would carry out the other steps.

One caveat, some of the programs may not have ever had any records, so the field on the subreport has to be passed in to the function and not the value in the field, otherwise it displays as #Type on the report.

I tried the following without success, but I'm hoping it will help explain what I'm trying to accomplish:

Expand|Select|Wrap|Line Numbers
  1. //The expression is
  2. =TimelinessPercent([Col1],"ABC"]
  3.  
Expand|Select|Wrap|Line Numbers
  1. //My attempt at the standard module function
  2. Public Function TimelinessPercent(col As Long, prog As String)
  3.  
  4.     Dim fld As Field
  5.  
  6.     Select Case Program
  7.         Case "ABC"
  8.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!ABC
  9.         Case "DEF"
  10.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!DEF
  11.         Case "GHI"
  12.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!GHI
  13.         Case "JKL"
  14.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!JKL
  15.         Case "MNO"
  16.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!MNO
  17.         Case "PQR"
  18.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!PQR
  19.         Case "STU"
  20.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!STU
  21.         Case "VWX"
  22.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!VWX
  23.         Case "YZA"
  24.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!YZA
  25.         Case "BCD"
  26.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!BCD
  27.         Case "EFG"
  28.             Set fld = Reports!rptTimeliness!srptTimelinessTotalsByProgram.Report!EFG
  29.     End Select
  30.  
  31.     Debug.Print "str: " & prog & "  col: " & col & "  fld: " & fld
  32.  
  33.     If (col = 0) And (fld = 0 Or IsNothing(fld)) Then
  34.         TimelinessPercent = 0
  35.     ElseIf (col / fld > 0.00000001) And (col / fld < 0.05) Then
  36.         TimelinessPercent = (col / fld) + 0.005
  37.     Else
  38.         TimelinessPercent = col / fld
  39.     End If
  40.  
  41. End Function
  42.  
I get the error message that says the subreport item is closed or doesn't exist when I try to run this.

Because of this, I think I need to pass the actual field from the subreport to the function instead of a string since I can't set a field object for a field on a subreport. I just don't know how to accomplish this.

Thanks,
beacon
Jun 15 '12 #1
Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
There are a few things I find a bit confusing with the code samples you have provided. First off, I dont know of any IsNothing function in access, so either you have typed it wrong, or you have defined such a function yourself.

Secondly, your last bit of code takes "prog" as input parameter yet does a select case on "Program". You should ALWAYS have Option Explicit at the top of all your modules.

With your first bit of code, I just wanted you to know there is a simpler way of doing it, using the format value for the textbox.

If the textbox is bound to a numeric field (Which I presume ABC is) you can specify how it should format positive, negative, zero and null (in that order). An example shown below:
Expand|Select|Wrap|Line Numbers
  1. Format: #;(#);"Zero";"Null"
That will make positive numbers be shown normally, negative numbers be shown with a parenthesis around them and 0 be shown as a textstring "Zero" and null as a textstring "Null". This can be quite handy. Note that it does in no way change the value of the data stored in the field, and should I reference textbox txABC bound to field ABC with a value of 0 for example:
Expand|Select|Wrap|Line Numbers
  1. Msgbox "Textbox txABC contains:" & me.txABC
it would report "Textbox txABC contains:0" and NOT "Textbox txABC contains:Zero"

Im sorry however to say, that I simple don't understand what your caveat is about.
I think what you want to accomplish should be done at the query level instead of in the report, and you should look to solve it there, or return with more information for me or someone else to assist you.
Jun 16 '12 #2

beacon
100+
P: 579
Thanks for taking the time Smiley...I'll try to clarify a few of the things you had questions about.

There are a few things I find a bit confusing with the code samples you have provided. First off, I dont know of any IsNothing function in access, so either you have typed it wrong, or you have defined such a function yourself.
IsNothing is a custom function I use that checks for null, empty, zero, and a couple of other conditions that might cause a value to be "nothing", per se.

Secondly, your last bit of code takes "prog" as input parameter yet does a select case on "Program". You should ALWAYS have Option Explicit at the top of all your modules.
This was a typo that I missed when asking my question. I had tried a bunch of different things and failed to change prog to Program, or vice versa. Either way, whether I used prog in both places or Program in both places, my function still did not work. I always use Option Explicit...unfortunately there's no Option Explicit when typing our questions here in Bytes to prevent my mistake! =)

With your first bit of code, I just wanted you to know there is a simpler way of doing it, using the format value for the textbox.

If the textbox is bound to a numeric field (Which I presume ABC is) you can specify how it should format positive, negative, zero and null (in that order). An example shown below:

Format: #;(#);"Zero";"Null"
I probably misstated the part about ABC being bound. It's actually a field on a cross tab query, where "ABC" is a column heading in the cross tab's properties, and the value is a calculation...a count of documents that weren't completed within a timeframe.

With that in mind, I appreciate the formatting tip, however, this part of my code actually works fine. It's the numerator in the division I use in the expression and I just needed it to be zero if the value was nothing. The output of the other text box I'm working with, that uses the rounding expression, needs to be formatted as percent (which is the main reason why I created the expression that rounds the value in the first place). The reasoning for it was to force a value to appear as zero percent on the report if the value is "nothing". Your suggestion may very well work and I look forward to giving it a test, but for the time being, I need to focus on the text box with the rounding expression.

Im sorry however to say, that I simple don't understand what your caveat is about.
My objective is to turn the following expression into a standard module function so that I can reuse it without having to type so much into so many text boxes:

Expand|Select|Wrap|Line Numbers
  1. //This is basically an expression that checks for a value
  2. //or rounds to a certain number of decimal places.
  3. =IIf(([Col1]=0) And ([srptTimelinessTotalsByProgram].[Report]![ABC]=0 Or 
  4. IsNothing([srptTimelinessTotalsByProgram].[Report]![ABC])),0,
  5. IIf(([Col2]/[srptTimelinessTotalsByProgram].[Report]![ABC]>0.00000001) And 
  6. ([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]<0.05),([Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC])+0.005,
  7. [Col1]/[srptTimelinessTotalsByProgram].[Report]![ABC]))
  8.  
The code basically rounds the result of dividing [ColX] by [srptTimelinessTotalsByProgram].[Report]![Y], where 'X' is a number related to a column on the report and 'Y' is a program field on the subreport.

I'm sure it's probably possible to accomplish what I'm trying to do with the rounding on a query, but I unfortunately don't have the time to rework the queries that are supplying this and other reports.

So, to reiterate my question, is it possible to pass the field on a subreport to a standard module function so that I can access the value and return it to an expression?

The caveat I spoke of occurs when I try to pass the value in the field on the subreport to the standard module function. If I try to pass the following:

Expand|Select|Wrap|Line Numbers
  1. =TimelinessPercent([Col1],[srptTimelinessTotalsByProgram].[Report]![ABC])
  2.  
...to my slightly revised standard module function so that it looks like this:

Expand|Select|Wrap|Line Numbers
  1. //My attempt at the standard module function
  2. Public Function TimelinessPercent(col As Long, fld As Long)
  3.  
  4.     If (col = 0) And (fld = 0 Or IsNothing(fld)) Then
  5.         TimelinessPercent = 0
  6.     ElseIf (col / fld > 0.00000001) And (col / fld < 0.05) Then
  7.         TimelinessPercent = (col / fld) + 0.005
  8.     Else
  9.         TimelinessPercent = col / fld
  10.     End If
  11.  
  12. End Function
  13.  
...then the function works for all the text boxes on the report where a value has been entered in for that program (in [srptTimelinessTotalsByProgram].[Report]![ABC]). However, if the value for the program is null, then the output on the report from the standard module function I've used in the expression is #Type. I don't want #Type to display on the report if [srptTimelinessTotalsByProgram].[Report]![ABC] is null.

So, the only way I can determine this would work, since it worked fine when I included the entire expression in the text box, was to somehow pass the subreport field to the standard module function before carrying out the steps to check the value and round it accordingly.

I understand this is a very long, complicated, and probably not ideal situation (or way to handle the situation), but I'm really just interested in determining whether I can pass the subreport field to the standard module function so I can use it to return the value that will display as a percent on the report. If there isn't a way to do that, that's fine...I can always go back to typing the full expression into the text box, but I was hoping to learn something and save myself from having to type so much in the future.

Thanks,
beacon
Jun 16 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
I think I understand better now. I thought you wanted to refer to a record which didn't exist, but now I believe what you want to do is to refer to a FIELD (which can be null at times) on a record.

As I see it now, the problem is that your function expects to receive a Long, and sometimes you pass it Null. That is not acceptable to VBA. This is a case where the Variant datatype can come in handy, or the NZ function.
The NZ([Field],[Value]) will check for null, and replace the null with a safe value of your choice. Example below:
Expand|Select|Wrap|Line Numbers
  1. TimelinessPercent([Col1],Nz([srptTimelinessTotalsByProgram].[Report]![ABC],0))
Now if [srptTimelinessTotalsByProgram].[Report]![ABC] Is not null, the value in the control will be passed. If it IS null, the nz will convert it to 0 before passing it along.



The OTHER way to approach it is to allow your custom function to accept null, by changing the incoming datatype from long to Variant.
Expand|Select|Wrap|Line Numbers
  1. Public Function TimelinessPercent(col As Long, fld As Variant)
  2.     If (col = 0) And (fld = 0 Or IsNothing(fld)) Then
  3.         TimelinessPercent = 0
  4.     ElseIf (col / fld > 0.00000001) And (col / fld < 0.05) Then
  5.         TimelinessPercent = (col / fld) + 0.005
  6.     Else
  7.         TimelinessPercent = col / fld
  8.     End If
  9.  
  10. End Function
Assuming your IsNothing function works properly, you shouldn't need to change anything more.
Jun 18 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.