Select Case Function

[csolomon]

Hello:

I am creating a form that will calculate a value based on the value selected from a case statement in a function. The function I created is called GetYield and accepts 3 arguments.

I have a continuous form that has a combo box (cbo_matTypeID) to allow the user to select the material type and based on the user selection, another combo box (cbo_materialID ) is populated. Here is my function:

Public Function GetYield(BatchWeight As Double, MatGrav As Double, matType As Integer) As Double
'returns the yield of each material 'DM_yield' calculation
Batchweight refers to the matBatchweight on the form
MatGrav refers to the DM_Gravity text box on the form
matType refers to the cbo_matTypeID combo box on the form
 
Select Case cbo_matTypeID
'User selects from cbo_matTypeID (Cement, Coarse, Fine, Pigment)
Case 1, 2, 3, 4
GetYield = BatchWeight / (MatGrav * 62.4)
'Chemicals
Case 5
GetYield = (BatchWeight / 128) * (10 / 62.4)
End Select
End Function

I expected that when the user makes a selection from the combo box, the yield would be calculated based on the material type that was selected. That does not happen; instead I get the same 0.00 for all the materials that have been selected.
Here is what I have in my unbound text box that has the calculation, called DM_Yield:
=GetYield([matBatchWeight],[DM_Gravity],[cbo_matTypeID])

matBatchWeight refers to a user input field, and DM_Gravity refers to a bound text box that populates based on the specific material that is selected from the cbo_materialID box.

any assistance would be appreciated.

[Stewart Ross]

Hi. Most likely cause is that you have accidentally substituted the combo name cbo_matTypeID for your parameter matType in line 7 above. It is likely to be treated as an undeclared variable of variant type by VBA, null in value by default, which will result in your Case statement not being executed.

You should enable the option for using explicit variable declarations to guard against this problem (Tools, Options from VB Editor) - this will place compiler directive Option Explicit in your code module to force the compiler to generate error messages if you do not declare variables before use.

-Stewart

[csolomon]

Hi Stewart,

I appreciate your response.

You are right, I was using the combo box name on the form as opposed to the argument in the function representing the combo box name.

You are a genius! Thank you