Failing to capture values correctly?

[Risky Dave]

Hi,

Can someone please explain why this isn't working? I am trying to capture
two values entered into a form by a user - Cost Score and Cost Estimate. Cost
Score is a value of 1 - 5 and Cost Estimate is a value in £'s. Each of the 5
possible Cost Scores has a range of Cost Estimates that is valid for it and
this code is trying to check that an appropriate Estimate has been made
against the declared Score. If an appropriate Estimate has not been made, an
error message is returned.

When this runs with legitimate values entered in TextBoxes 16 and 17, it
still returns the error message and I don't understand why so any
observations would be much appreciated.

Sub BAUCostCheck()
Dim lCostScore As Long ' used to capture the assessed cost score
Dim lCostEst As Long ' used to capture the estimated cost

lCostScore = CLng(FmRiskCost.TextBox16.Value)
lCostEst = CLng(FmRiskCost.TextBox17.Value)

Select Case True
Case lCostScore = "1"
<do stuff>
Case lCostScore = "2"
<do stuff>
Case lCostScore = "3"
If lCostEst > 50000 and lCostEst < 100000 Then
MsgBox "Medium cost must be between £50,000 and £100,000",
vbExclamation, "Cost Estimation Error"
FmRiskCost.TextBox19.Value = ""
FmRiskCost.TextBox22.Value = ""
FmRiskCost.TextBox24.Value = ""
FmRiskCost.TextBox25.Value = ""
FmRiskCost.TextBox26.Value = ""
End If
Case lCostScore ="4"

 

[Risky Dave]

Ooops!

Apologies, the the actual codeis this. The error is the same:

Sub BAUCostCheck()
Dim lCostScore As Long ' used to capture the assessed cost score
Dim lCostEst As Long ' used to capture the estimated cost

lCostScore = CLng(FmRiskCost.TextBox16.Value)
lCostEst = CLng(FmRiskCost.TextBox17.Value)

Select Case True
Case lCostScore = "1"
<do stuff>
Case lCostScore = "2"
<do stuff>
Case lCostScore = "3"
If lCostEst < 50000 or lCostEst > 100000 Then
MsgBox "Medium cost must be between £50,000 and £100,000",
vbExclamation, "Cost Estimation Error"
FmRiskCost.TextBox19.Value = ""
FmRiskCost.TextBox22.Value = ""
FmRiskCost.TextBox24.Value = ""
FmRiskCost.TextBox25.Value = ""
FmRiskCost.TextBox26.Value = ""
End If
Case lCostScore ="4"

 

[JLGWhiz]

One glaring glitch is declaring lCostEst as Long. If it is currency, in
Pounds, then all of your change will be truncated. I think lCostEst As
Double would be better.

 

[JLGWhiz]

Well, maybe not, if you are not using decimal places in your values.

 

[JLGWhiz]

I am having trouble understanding your Select Case statement. How does the
True criteria translate to the lCostScore Case? It would make more sense if
It read:

Select Case lCostScore
Case 1
'Do stuff
Case 2
'Do other stuff

Etc.

 

[Risky Dave]

Just to tidy this one up, and to respond to JLGWhiz, I'll try and explain
(briefly!) what's going on and what the problem was.

This is part of a much larger (several thousand lines of code and growing)
application that does a ton of stuff, most of which is driven by data entered
by the user through various forms.This particular form is used by project
managers to estimate the cost of risks to their project and links to a part
of the workbook where the risk is scored (called a risk assessment). Amongst
other things, the risk assessment requires the user to assign a value of 1-5
against the probability of a risk occurring. This is the value that is
captured by the lCostScore variable.

On this form, the project manager is asked to actually enter a breakdown of
the costs associated with the risk in detail, which is why the lCostEst
variable capture £'s. For my purposes, I am happy to only use whole numbers
here (this is, after all, an estimate ).

For each lCostScore value between 1 and 5 there is an agreed range of costs.
In this particular example, the lCostScore value of 3 equates to a monetary
range of £50,000 - £100,000. I can therefore use the lCostScore value as a
quality check of the lCostEst estimate. The select case examines the stated
lCostScore value (1,2,3,4,5) and checks that the inputted lCostEst value is
in the range £50,000 - £100,000. If it isn't, then certain fields on the
input form are blanked out and the user is forced 9by other event code) to
either cancel the data entry or put corrected values in.

My particular error was being caused by the blanking out of TextBox19. This
was a typo in the code and should have been TextBox17. When these boxes are
changed, a whole load of event code kicks in to do a variety of stuff which
eventually led to the erro being generated. Obviously, as good as you are on
this forum, I don't think you would have been able to find that one , but
thanks for trying.

Thanks for the interest anyway - and all the help you have all been whilst
I've been developing this thing. Keep up the good work!

Dave

 

[JLGWhiz]

Yep, since the error message was not included in the posting, I was really
just guessing a what the problem might be anyhow. Glad you found the problem
and thanks for posting back.