How to compare and control in VBA two numeric TextBoxes fields 3

[polocar]

Hi,
I’m developing a form in Visual Basic Access (I use Access 2003).
In the form I have two TextBox controls (lets call them txt1 and txt2) in which the user must insert integer values.
The integer value in txt1 must be >= than the integer value in txt2. If the user doesn’t respect this rule, I would like that a warning message appeared and the focus returned to the last TextBox written (so the user can correct it).
I have tried to set the ValidationRule property of each TextBox in the Properties window of the form:
For txt1 I have set it to “>=[txt2].[Text]”
For txt2 I have set it to “<=[txt1].[Text]”
(the “[“ and “]” parenthesis have been added automatically by Access)
In this way it doesn’t function, because the comparison is between two strings, not between two integers (so “20” is < “3”).
So, I have modified the ValidationRule properties as follows:
For txt1 I have set it to “CInt([txt1].[Text])>=CInt([txt2].[Text])”
For txt2 I have set it to “CInt([txt2].[Text])<=CInt([txt1].[Text])”
The result is that the validation rule is never satisfied! (the ValidationText message always appears, also when it should not).
So, I tried to handle the problem by code:

Private Sub txt1_BeforeUpdate(Cancel As Integer)

    If (CInt(txt1.Text) < CInt(txt2.Text)) Then
        MsgBox "txt1 must be >= than txt2!", vbOKOnly, "Error"
        Cancel = True
    End If

End Sub

Private Sub txt2_BeforeUpdate(Cancel As Integer)

    If (CInt(txt2.Text) > CInt(txt1.Text)) Then
        MsgBox "txt2 must be <= than txt1!", vbOKOnly, "Error"
        Cancel = True
    End If

End Sub

but there is a problem: to read the other TextBox text, Access wants that it has the focus (with this code it raises an error).
So, I have tried in this way (I write only the txt1 code, the other is similar):

Private Sub txt1_BeforeUpdate(Cancel As Integer)

    Dim n1 As Integer
    Dim n2 As Integer
    
    n1 = CInt(txt1.Text)
    txt2.SetFocus
    n2 = CInt(txt2.Text)
    If (n1 < n2) Then
        MsgBox "txt1 must be >= than txt2!", vbOKOnly, "Error"
        Cancel = True
    End If

End Sub

but now another error raises: “It is necessary to save the field before executing the action or the method “GoToControl” (I hope my translation from italian is correct).

How can I solve this problem? Have you any suggestion to give me?

Thank you very much

 

[PHV]

problem: to read the other TextBox text, Access wants that it has the focus
Use the Value property instead of Text

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

 

[SgtJarrow]

Try the following....Note this belongs in the AFTERUPDATE event....not the BeforeUpdate as you are trying to use. And change slightly to fit for the txt2 box.

Private Sub txt1_AfterUpdate(Cancel As Integer)

    If Me.txt2 <> "" And Not IsNull(Me.txt2) Then
        If CInt(Me.txt1) < CInt(Me.txt2) Then
            MsgBox "txt1 must be >= than txt2!", vbOKOnly, "Error"
            Me.txt1 = ""
            Me.txt2.SetFocus
            Me.txt1.SetFocus
        End If
    End If

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Ooops....take the (Cancel As Integer) of the end of the first line......

Hit Submit too fast

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[missinglinq]

Just as an aside, IMHO, best always to use .Value rather than .Text so you don't have to worry about where the focus is at! Don't know of any downside to this. Using Before_Update always seems like a good idea, until you try to do something like Goto whatever, or in a validation routine that fails, resetting the focus on the offending control! Because of that I usually follow mstrmage1768's lead and use the After_Update event.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[polocar]

Thank you guys, in this way I solved my problem