468,469 Members | 1,971 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,469 developers. It's quick & easy.

Select Case Function

166 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function GetYield(BatchWeight As Double, MatGrav As Double, matType As Integer) As Double
  2. 'returns the yield of each material 'DM_yield' calculation
  3. Batchweight refers to the matBatchweight on the form
  4. MatGrav refers to the DM_Gravity text box on the form
  5. matType refers to the cbo_matTypeID combo box on the form
  6.  
  7. Select Case cbo_matTypeID
  8. 'User selects from cbo_matTypeID (Cement, Coarse, Fine, Pigment)
  9. Case 1, 2, 3, 4
  10. GetYield = BatchWeight / (MatGrav * 62.4)
  11. 'Chemicals
  12. Case 5
  13. GetYield = (BatchWeight / 128) * (10 / 62.4)
  14. End Select
  15. 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.
Dec 2 '08 #1
2 2460
Stewart Ross
2,545 Expert Mod 2GB
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
Dec 2 '08 #2
csolomon
166 100+
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
Dec 2 '08 #3

Post your reply

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

Similar topics

6 posts views Thread by Jack | last post: by
9 posts views Thread by Kevin | last post: by
4 posts views Thread by Terencetrent | last post: by
8 posts views Thread by | last post: by
4 posts views Thread by Michel | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.