Err.Number Problem

[DanAtCDS]

I am trying to set up a small bit of error checking code in order for my end users to get a "second chance" at entering valid data.
They wanted the ability to "add" numbers together while counting documents without using a calulator (or God forbid they use their head!!!) SO they are entering a string such as:
"5+10+5+15" into a text box and getting the results displayed in a new text box.
BUT
I know my end users!!! So what if they try to enter a - (minus) sign??
IF they do, the error message that is displayed is "run-time error 13: type mismatch" Why does the following code not work to trap this particular instance of the error.

Dim arr
Dim t As Variant

arr = Split(Me.txtCount, "+")

Dim TotalCount As Integer
TotalCount = 0
For Each t In arr
    If t = "" Then
        GoTo ExitLoopBlank
    ElseIf Err.Number = 13 Then
       GoTo ExitLoopTMisMatch
    Else
        TotalCount = TotalCount + t
    End If
Next t

Me.txtTotal = TotalCount

ExitLoopBlank:
Me.txtTotal = TotalCount
Exit Sub

ExitLoopTMisMatch:
MsgBox "There has been a type mismatch, ... " & Chr(13) & Chr(10) & "Please check the data that was entered and try again.", vbCritical, "Entry Error!"
Me.txtTotal.SetFocus
Me.txtCount.SetFocus
Exit Sub

Please help me understand why this doesn't work! I've been searching out here all morning to hopefully help myself but can't seem to find the exact answer.

All assistance is much appreciated!
Dan

 

[EBGreen]

It doesn't work because there hasn't been any error raised yet at the point where you check for it. The error is raised on this line:
TotalCount = TotalCount + t

The way that I would handle it (this is not tested):

Dim nTotal As Integer
On Error Resume Next
nTotal = Evaluate(Me.txtTotal)
If Err.Number <> 0 Then
GoTo ExitLoopTMisMatch
End If
Me.txtTotal = nTotal

ExitLoopBlank:
Exit Sub

ExitLoopTMisMatch:
MsgBox "There has been a type mismatch, ... " & Chr(13) & Chr(10) & "Please check the data that was entered and try again.", vbCritical, "Entry Error!"
Me.txtTotal.SetFocus
Me.txtCount.SetFocus
Exit Sub

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

As an after thought:

That should let them enter any valid numeric expression. If you only want them to be able to enter '+', then you would need to do some checks on the string before you evaluate it.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[DrSimon]

I think it's failing because Spilt makes t evaluate to say '1-2' which isn't an integer, so a type mismatch. I forget how unreasonable people can be at times so here's a (inelegant) solution that works. It copes with all normal signs in a strictly left to right order. However it won't work if the b*****s use a real number!

Dim TotalCount As Integer
Dim P As Integer
Dim A As Integer
Dim NumString As String
Dim Num(20) As Integer ' 20 should be enough!?
Dim Sign(20) As String
Dim NumCount As Integer


TotalCount = 0
NumString = ""
NumCount = 0
For P = 1 To Len(Me.TxtCount)
A = Asc(Mid(Me.TxtCount, P, 1))
Select Case A
Case 48 To 57 ' Digits
NumString = NumString & Chr(A)
Case 42, 43, 45, 47 ' *, +, -, /
NumCount = NumCount + 1
Num(NumCount) = Val(NumString)
NumString = ""
Sign(NumCount + 1) = Chr(A)
End Select
Next P
NumCount = NumCount + 1
Num(NumCount) = Val(NumString)

TotalCount = Num(1)
For P = 2 To NumCount
If Sign(P) = "+" Then TotalCount = TotalCount + Num(P)
If Sign(P) = "-" Then TotalCount = TotalCount - Num(P)
If Sign(P) = "*" Then TotalCount = TotalCount * Num(P)
If Sign(P) = "/" Then TotalCount = TotalCount / Num(P)
Next P

Me.txtTotal = TotalCount


Simon Rouse

 

[EBGreen]

did you try the evaluate solution I posted?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[DanAtCDS]

It doesn't like the Evaluate expression?!?! It tells me that the "sub or function is not defined"

 

[DanAtCDS]

Dr. Simon,
Your solution does work, ... however, I need the error checking code to work more so than I need the ability to evaluate any expression. Thanks for the info though!! I think I'll keep it in my "code library" in case it's ever needed!

Thanks,
Dan

 

[PHV]

And what about Eval(Me.txtCount) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DrSimon]

Hey Guys - I didn't know about Eval. Apologies - it's far better than my little noddy.
As for the error trapping, you have to set an On Error ... statement to enable error trapping before you can do something like 'ElseIf Err.Number = 13'
Simon Rouse