472,985 Members | 2,807 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,985 software developers and data experts.

Select Case Function

166 100+

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
  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:

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 2635
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.

Dec 2 '08 #2
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

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

Similar topics

by: Jack | last post by:
Hello, <%@ Language=VBScript %> <% Response.Write "<FORM name=form1>" Response.Write "<select name=""select1"">" Response.Write "<option value=1>One</option>" Response.Write "<option...
by: Kevin | last post by:
Hi, I am getting a syntax error Microsoft VBScript compilation error '800a03ea' Syntax error On the code below. The error references the "End Select" line Can anyone help me with what I am...
by: Terencetrent | last post by:
I having been using Access '97/2002 for about 4 years now and have never really had the need or the time to learn visual basic. Well, I think the time has finally come. I need help with Visual...
by: deko | last post by:
When I loop through this function, it works fine until it hits End Function - then it jumps to End Select. Very strange... This behavior occurs when Case = 255. Any ideas why this is happening? ...
by: MLH | last post by:
After running the following code snippet... MyURL = "http://tycho.usno.navy.mil/what.html" msXML.Open "GET", MyURL, False msXML.send I would like to execute code to perform essentially what...
by: | last post by:
Hello, This is gonna sound real daft, but how do I test a Select Case statement for variants of a theme? Here's a snippet of my code... Select Case sUsr Case "Guest", "TsInternetUser",...
by: Michel | last post by:
Hi, How can I use then Windows' color picker from Access? Thx
by: Henning M | last post by:
Hi all, I having some problems with Access and selecting records between dates.. When I try this in access, it works fine!! "Select * from Bilag Where Mdates Between #1/1/2006# And...
by: microsoft.public.dotnet.languages.vb | last post by:
Hi All, I wanted to know whether this is possible to use multiple variables to use in the select case statement such as follows: select case dWarrExpDateMonth, dRetailDateMonth case...
by: Max | last post by:
Is there any way to set a select-multiple type <select multiple="multiple"with multiple selected options in scripting? Any idea about this is appreciative.
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.