Help with Module coding!

[mongous]

I can't seem to get my code to work. I am trying to attach a procedure ta control on a report.

Function Percentage(DCA As Integer, SiteDCA As Integer)

Dim Team As String
Dim Pct As Integer

Team = Reports![rptRepStats_MTDReport]![Team]
DCA = Reports![rptRepStats_MTDReport]![DCA]


    Select Case Team
        Case "BHM Lisa"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![BhmDca]
            
        Case "CSC Barbara", "CSC Carly", "CSC Judy", "CSC Leah", "CSC Steve"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![SeaDca]
            
        Case "CSC Deanne"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![MyghcDca]
            
        Case "CW CS Agents"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![CwaDca]
            
        Case "SPO CS Agents"
             SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![SpkDca]
             
        Case Else
            MsgBox "Cannot locate team", 0, "Team Error"
    End Select
    
Pct = DCA / SiteDCA

End Function

Basically it is just supposed to come up with a percentage base on two numbers: one within the report and the other within a nested subreport.

The problem is when it is ran, I keep getting prompted for the values of my variables (DCA and SiteDCA). I THOUGHT I declared and set them in my code!?!?!?

 

[rdodge]

Your code is a bit confusing. Let me explain:

Function Percentage(DCA As Integer, SiteDCA As Integer)

This above indicates that you are calling on this function with whatever is calling on this function also supplying the 2 integer values as required by how you have this particular line setup.

No where do I see you actually using the values as would be provided by the procedure/function that called on this function.

If you meant to have these variables declared within the procedure, but not provided by another function/procedure, then you should have done the following instead:

Function Percentage()
Dim DCA As Integer, SiteDCA As Integer
.
.
.
End Function

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Steve101]

The design of the application also appears to be a bit suspect. You should not be hard coding people's names and companies into the code (as in the CASE statement). This is not good programming practice. You should be holding the SITEDCA value associated with each person, as an item of data which you are passing through to the report for each record. Then all of the CASE logic would not be necessary AND you would be able to add people to the database without requiring modification to the code.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[rdodge]

In addition to Steve's comments, as I didn't notice this at first, hard coding personal information can get you into issues that you don't want to get into.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[mongous]

A-ha, ok so I am always confused about passing the parameters. So I tried that and I still get prompted for DCA and SiteDCA. I just click ok (to get to the report) and in the field where the result should be displayed, I get the #Name? error.

 

[rdodge]

Within the Function code itself, whatever you want the fucntion to return as a result, you must let the function's name = the result you want returned. Example:

Instead of:

Pct = DCA / SiteDCA

Use:

Percentage = DCA / SiteDCA

As Percentage is the name of the function.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[mongous]

You should be holding the SITEDCA value associated with each person, as an item of data which you are passing through to the report for each record.

I am not sure what you mean. The SiteDCA value is a number (daily call average) derived from a calculation on one of the subreports. There are 5 different ones, for different sites. I thought I would have to basically lookup which site each member belonged to so I can calculate the DCA for that person based on the correct numbers?

 

[Steve101]

Mongous,

I dont really understand your application enough to be able to comment too much further on it. Generally speaking though, its not a good idea to hardcode peoples names into applications. What happens eg. if a team leader person leaves?

Are all of the 5 subreports differently structured; do they contain different sorts of information. If not, perhaps you should be using a single report with Grouping by Team.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[mongous]

Yeah, they all contain different data. There are 3 subs within my main report. There are 5 different sites (call centers) that need their DCA (daily call average) calc'd seperately. Then, each record (reps) needs to compare their own personal DCA against the Site DCA as a whole to get the percentage. Any ideas?

 

[Steve101]

your approach seems OK (without seeing the system) notwithstanding my earlier comments.

good luck

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[bboffin]

Since the subject of this thread includes a reference to 'Module' I assume that this code is in a module and not the Report class.

If so then the basic definition of the function might be better expressed as:

Public Function Percentage(ByVal DCA As Integer, ByVal Team As String) as Double

Thus CDA and Team are arguments passed into the function thereby removing the need for the hard coded references to report fields and the function will return the percentage as a double.

The brings us to the nub of the problem. The function is being invoked incorrectly as the parameters are not being supplied which is why the prompts occur.

It should be invoked something like this:

Percentage(Reports![rptRepStats_MTDReport]![DCA],Reports![rptRepStats_MTDReport]![Team])

Alternatively remove all the arguments from the function definition and leave the code as it is apart from declaring SiteDCA as an integer and assigning the result to Percentage as suggested above.

 

[mongous]

a-ha! that makes sense to me! Thank you ver much! I am off for the holiday weekend but will try it upon return to work on Tuesday. Thanks!

 

[mongous]

Ok so I took bboffin's suggestions and here's what I have in the module itself:

Public Function Percentage(ByVal DCA As Integer, ByVal Team As String) As Double


Dim SiteDCA As Integer


    Select Case Team
        Case "BHM Lisa"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![BhmDca]
            
        Case "CSC Barbara", "CSC Carly", "CSC Judy", "CSC Leah", "CSC Steve"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![SeaDca]
            
        Case "CSC Deanne"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![MyghcDca]
            
        Case "CW CS Agents"
            SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![CwaDca]
            
        Case "SPO CS Agents"
             SiteDCA = Reports![rptRepStats_MTDReport]![subCSCDCAs]![SpkDca]
             
        Case Else
            MsgBox "Cannot locate team", 0, "Team Error"
    End Select
    
  Reports![rptRepStats_MTDReport].txtPct = DCA / SiteDCA

End Function

And here is what I have in the 'control source' for the txtPct text box (which is where the function is invoked):

Percentage(Reports![rptRepStats_MTDReport]![DCA],Reports![rptRepStats_MTDReport]![Team])

So it seems to run, however the Select Case doesn't seem to be working. I always get the error MessageBox (Case Else).

Basically there are three parameters: DCA, SiteDCA, and Team. The function must look up which team is associated with each record to determine the value to use as SiteDCA.

I don't understand why the Select Case won't work.
Would it be easier/better to put the code in the Report Class itself?

 

[rdodge]

What is the value of the Team argument that the parent procedure/function is passing to this function? If the value does not match to any of the values as shown in the Case lines, then it will branch out to the Case Else line.

In the Parent procedure/function, be sure it's passing the argument either with it's argument name or by using commas to separate between arguments of the function call.

TeamP = Percentage(8,Team:="CSC Deanna&quot

TeamP = Percentage(8,"CSC Deanna&quot

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[mongous]

Well it has to match one of the case choices, that is all that exist.

The Team argument is supposed to be 'looked up' by the Select Case statement, for the sole purpose of determining WHICH SiteDCA argument to use in the ultimate formula: DCA/SiteDCA.

 

[rdodge]

Have you checked the code that's calling on this function and made sure it's passingone of the valid values? This is normally done in the debugging stage, when you run into an issue like this. It can be checked for by using break points and the watch window, should you need it.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.