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_SingleSampWt' 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.

[csolomon]

Hi Chip,

I tried to hover over the actual calculation, but nothing happened when i hovered over the variable names. My day is over for today, but if I will be here bright and early tomorrow and I would appreciate if you could assist me.

Thank you for all your help today.

[ChipR]

I suspect that you aren't referencing the field values that you want, but I'm about to leave work also. We'll figure it out tomorrow.
When you get back, can you upload the form?

[ChipR]

Here's a question: What is the matBatchWeight control, and when is its content and the content of the txtCemSum set?

[csolomon]

Hey Chip,

I have been trying to reduce the size of my DB so that I could attach it, but I have not been successful. Is there an email I can send it to?

the matBatchWeight control is an input value. Each material that is used to create a design mix, uses a certain amount (lbs or ozs) of a material. The content of matBatchWeight is set after the user selects the the material they are using, they then input the amount they are using. txtCemSum is a calculation. it is set after the user selects the cement they are using...they can select up to two. txtCemSum is the total sum of the matBatchWeights used for the two cements selected.

[ChipR]

As I suspected. The subform is calculated when the form loads, and the values are empty. Place a button on your form somewhere and have it do
subform.form.requery
or
subform.form.recalc
I'm not sure what the difference is and can't look it up at the moment, for after the values are entered.

[csolomon]

The .requery and .recalc are basically the same. I used .recalc

Private Sub Command46_Click()
[SF_MixSample].Form.Recalc
End Sub

But my Cases for 1, 2, and 3 still show as #error

[csolomon]

Also, matBatchWeight is a bound column, so the values have already been entered

[ChipR]

Sent you a private message with an email address. I just can't figure it out without seeing what's what on the form I guess.

[csolomon]

Chip,

I sent the zip file to your email.

[ChipR]

Turns out, it won't even call your function if you don't have the proper number of arguments, which happens when [txtPigPercent] is null.
Change that in your function call to Nz([txtPigPercent],0) or some number other than zero that you would want to substitute.
Also the form complained when the function returned null, so return something else in that case.

[csolomon]

Thanks chip, that worked perfectly.