Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Validation problem

    I have the following code that is supposed to force a user to enter a value between $100 and $20,000,000 into a text box if they have selected the option button in front of it. If they have entered an invalid amount, the background is to turn yellow, and the invalid value is to be set to blanks so they can start again. They can at any time however decide they don't want to update this text box and can de-select the option button, at which time, the background should go back to white.

    This code is not working as planned and I cannot figure out why for the life of me.

    Code:
    Private Sub txt_Cat3_TargetAmt_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_ERROR
    
    Dim numeric As Boolean
    
    numeric = IsNumeric(Forms!frm_Main!txt_Cat3_TargetAmt)
                  
    If Forms!frm_Main!txt_Cat3_TargetAmt.Value < 100 Or Forms!frm_Main!txt_Cat3_TargetAmt.Value > 20000000 _
        Or Not (numeric) Then
    
        MsgBox ("Valid values for Target Amount are between $100 and $20,000,000 and must be numeric   "), vbInformation, "Target Amount Validation Error"
        Forms!frm_Main!txt_Cat3_TargetAmt.BackColor = 65535   ' set background to yellow
    
    Else
        Forms!frm_Main!txt_Cat3_TargetAmt.BackColor = RGB(255, 255, 255)  'white
    End If
    
    Exit_sub:                               ' Label to resume after error.
        Exit Sub                            ' Exit before error handler.
    
    Err_ERROR:                              ' Label to jump to on error.
        Call LogError(Err.Number, Err.Description, "Sub txt_Cat3_TargetAmt_BeforeUpdate()", Me.Name, , False)
        Resume Exit_sub
    
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Mornin ss

    Just a thought - why not keep it KISS (keep it simple and stupid)

    in the relevant textbox use for the cash input why not just set the the validation rule?

    ie Between 99 And 200

    and the validation text to your error message.


    gareth

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi ssmith001

    You do not say what the probelm is but this seems to work OK !

    Code:
    Private Sub txt_Cat3_TargetAmt_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_ERROR
    
    Dim numeric As Boolean
    
    numeric = IsNumeric(txt_Cat3_TargetAmt)
                  
    If txt_Cat3_TargetAmt.Value < 100 Or txt_Cat3_TargetAmt.Value > 20000000 _
        Or Not (numeric) Then
    
        MsgBox ("Valid values for Target Amount are between $100 and $20,000,000 and must be numeric   "), vbInformation, "Target Amount Validation Error"
        txt_Cat3_TargetAmt.BackColor = 65535   ' set background to yellow
        
        Cancel = True
    End If
    
    Exit_sub:                               ' Label to resume after error.
        Exit Sub                            ' Exit before error handler.
    
    Err_ERROR:                              ' Label to jump to on error.
    '    Call LogError(Err.Number, Err.Description, "Sub txt_Cat3_TargetAmt_BeforeUpdate()", Me.Name, , False)
        MsgBox Err.Description
        Resume Exit_sub
    
    End Sub
    
    Private Sub txt_Cat3_TargetAmt_Exit(Cancel As Integer)
        txt_Cat3_TargetAmt.BackColor = RGB(255, 255, 255)  'white
    End Sub
    The differances between this and your code are:-

    Removal of all referance to Form!frmMain! (my form had a different name!)

    Addition of Cancel = True (this prevents exiting the control without a 'valid' value)

    Addition of Exit event code to return to white (in place of Else part of If).

    Without a valid value Exit can also be achieved by using the escape key to return the value to the on entry value (including Null/Blank).

    Oh, also remed you LogError call.

    Without more info can't do mutch more.


    MTB

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    I tried adding the Cancel = True, but now if the user wishes to cancel and not update this field, the message box keeps popping up.

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    This should be rather simple. Either the user enters a valid value and I don't leave the field until they do, or they choose to unselect the option button, at which time they move on to a new field.

    I tried a simple validation rule but it kept firing off even they I chose to unselect the option button. That's not the desired result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •