VBA Function only working for some cases

[csolomon]

Hello:

I have a function that I created that returns the single sample weight of each material that is selected

My function accepts 5 arguments:
1)matType-The material the user has selected
2)batchWeight-The batchweight of each material used
3)SumCem-Sum of the cement material batchweights used
4)CemSingWt- Sum of cement single sample weight
5)pigPercent- the percent of pigments being used

Here is my function:
Public Function GetSingleWeight (matType As Integer, BatchWeight As Double, SumCem As Double, CemSingWt As Double, PigPerc As Double) As Double
'returns the single sample weight of each material in the 'DM_SingleSampW t' calculation

Select Case matType
'Cement
Case 1
GetSingleWeight = BatchWeight / 164.5
Case 2, 3
'Course, Fine
GetSingleWeight = CemSingWt / SumCem * BatchWeight
Case 4
'Pigment
GetSingleWeight = CemSingWt * 453.5924 * PigPerc
End Select
End Function

I have an unbound text box on my continuous subform called DM_SingleSampWt . the control source for this text box is:

=GetSingleWeight([cbo_matTypeID],[matBatchWeight],[txtCemSum],[txtCemSing],[txtPigPercent])

The problem is that only some of my Cases are being calculated. Cases 1, 2 and 3 do not work. I am receiving a #Error, error. If you notice, all of my cases do not use the same arguments, but I know it's necessary to have them when I call my function.
If you can offer any assistance, I would appreciate it.

Thank You

[ChipR]

Are any of your values zero or null? Specifically, you might have to say
GetSingleWeight = iif(SumCem = 0, 0, CemSingWt / SumCem * BatchWeight)

[csolomon]

No, none of them are zero or null; they will never be 0 or null. In Case 1 in this instance, SumCem is 658 and CemSingWt is 4.

[ChipR]

Is txtCemSum a text box set to Number format or a string?

[csolomon]

txtCemSum is set to a General Number

[ChipR]

Also, is it a text box on the detail of the continuous form? Can you set a breakpoint in your function and check the values passed in?

[csolomon]

No, it is a text box in the form footer of the continuous form.

How do I set a break point to check the values passed in?

[ChipR]

In the editor you should have a margin on the left hand side of your code that you can click in and set a red circle.

[csolomon]

OK...I've set the red circle for both Case 1 and case 2, 3...what am I supposed to be looking out for? something in the immediate window?

[ChipR]

When the form is opened and the function is called, it should stop there in the code. Hover the mouse over the variable names to see their values, or right-click them and set a watch as you step through the code.

[ChipR]

You can also put in the immediate window ?variablename and it will print out the value.

[csolomon]

Chip,

when I hover over case 1,
BatchWeight = 28

Which is incorrect. I have two instances on this continuous form where the case is 1 and the BatchWeight for those two instances are 526 and 132.

Also, when I type: ?cbo_matTypeID I get a blank line

[csolomon]

actually when i type ?matType, it =4

There is an instance of a record h aving the matType =4 but my function only works when the matType is 4.

[ChipR]

Make sure your breakpoints are on the calculation lines rather than the checks for case.

Select Case matType
'Cement
Case 1
* GetSingleWeight = BatchWeight / 164.5
Case 2, 3
'Course, Fine
* GetSingleWeight = CemSingWt / SumCem * BatchWeight
Case 4
'Pigment
* GetSingleWeight = CemSingWt * 453.5924 * PigPerc
End Select
End Function

And open the form and use the green arrow or F5 to continue until it stops on the case 2 calculation. Then check the values on that line.

[ChipR]

Also, put in a case else getsingleweight =0 or something. You should almost always have a case else.