Text box on a form SOMETIMES has problems

[Mimadocken]

I have a form with 3 textboxes on a TAB control within a form. It almost always works as expected, but every once in a while it decides I did things in the wrong order. I have attached a screen shot of the form and one of the code that is behind the bottom of the three textboxes.

The form allows entries for 3 situations. Textbox #1 allows you to enter a number of inches, which are translated into centimetres. Textbox #2 accepts a number of centimetres and returns the inches. Textbox #3 allows one to enter numbers and arithmetic symbols to use it as a small calculator. When one has entered numbers and symbols and presses Enter, the answer appears and one can continue with another part of the calculation or leave.

You should know that I'm not nearly clever enough to have come up with this code on my own. It was given to me, but unfortunately I'm unable to reach the author currently. If I understood it, maybe I could fix it on my own.

It gets uppity if I enter something in Cms and press the Tab key AND then I try to leave this tab. This can also happen if I simply click within the calculator field and then try to leave the form. It always works if I enter any number in this field before doing anything else. It then behaves like an angel.

I believe the area of my screen shot of the code was too small to include all of the longest lines of code. Therefore I'm pasting the entire line here.
Select Case MsgBox("Input '" & Me.txbCalculate & "' produced an error" & vbCr & vbCr & "Do you want to use the value as entered?", vbQuestion + vbYesNoCancel, "Input check")

You should also know that the error message I receive is "Runtime error 94. Invalid use of Null."

Here are the attachments.




Thank you for all your help, and especially for the help on this one.

 

[bob fitz]

Have you attached the correct screen shot.

The form allows entries for 3 situations. Textbox #1 allows you to enter a number of inches, which are translated into centimetres. Textbox #2 accepts a number of centimetres and returns the inches. Textbox #3 allows one to enter numbers and arithmetic symbols to use it as a small calculator. When one has entered numbers and symbols and presses Enter, the answer appears and one can continue with another part of the calculation or leave.

Perhaps I'm missing something but in the screen shot supplied, the first text box looks like it expects a weight, not a length.

 

[Mimadocken]

Well, of course you are right.

Here is the correct tab.

 

[bob fitz]

Can you copy/paste the code in a post.

 

[VilaRestal]

To understand it: It boils down to
txbCalculate = Eval(txbCalculate)
with slightly OTT error handling and comments

From the Access help:

"You can construct a string and then pass it to the Eval function as if the string were an actual expression. The Eval function evaluates the string expression and returns its value. For example, Eval("1 + 1") returns 2."

The Exit event happens whenever the focus shifts from the textbox.

The AfterUpdate event would be better I think and perhaps better still the click event of a button. As a user i would find it frustrating if it tried to do the calculation without me telling it to.

And it would be better I think if the answer appeared in a different textbox.

 

[VilaRestal]

So, if you put another textbox below txbCalculate and call it txbResult (and set it to locked and not enabled) and put a button on the form next to txbCalculate with the caption "Calculate" and call it cmdCalc then the following code:

Private Sub cmdCalc_Click()
    On Error Resume Next
    Err.Clear
    Dim sResult As String
    sResult = Eval(Me.txbCalculate)
    If Err Then
        Me.txbResult = "Error"
    Else
        Me.txbResult = sResult
    End If
End Sub

Would be simpler to understand and more user friendly than throwing up dialog boxes and forcing the focus to stay on txbCalculate.

 

[Mimadocken]

Bob, here is the code as you requested.
Option Compare Database
Option Explicit
Private Sub txbCalculate_Exit(Cancel As Integer)
Dim strInput As String, strOutput As String
Rem check whether a value is present
If Me.txbCalculate = vbNullString Then Exit Sub
Rem save the input
strInput = Me.txbCalculate
Rem inhibit fail on error
On Local Error Resume Next
strOutput = Eval(strInput)
Rem check whether evaluation worked
If Err.Number = 0 Then
Rem it did - use the result
Me.txbCalculate = strOutput
Else
Rem it didn't - ask the user what to do
Select Case MsgBox("Input '" & Me.txbCalculate & "' produced an error" & vbCr & vbCr & "Do you want to use the value as entered?", vbQuestion + vbYesNoCancel, "Input check")
Case vbYes ' user pressed 'Yes'
Case vbNo ' user presses 'No'
Rem highlight the input text
With Me.txbCalculate
.SelStart = 0
.SelLength = Len(strInput)
End With
Rem set cancel to retain focus with the text box
Cancel = True
Case Else ' user pressed 'Cancel' or closed the dialog box
Rem simply clear the offending input and move on
Me.txbCalculate = vbNullString
Rem if you want the user to re-enter something, set Cancel = True
End Select
End If
Rem restore fail on error
On Error GoTo 0
End Sub


VilaRestal,
Thank you. Those are interesting suggestions. I can put an answer field in, if that's what is needed to make this work. I must admit I had enjoyed the ability to "chain" the calculations, but it wouldn't break my heart if we needed to retype the answer and more of the problem into the original field to accomplish the same end.

Thanks everyone for the assistance on this. I'll gladly listen to your wisdom on the best way to proceed, and whether I NEED to go to two fields. I believe this is the last problem to solve before this project is complete, and you have all been an incredible help.