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

    Unanswered: Need help with validation routine

    Help! This is driving me nuts!

    I have a form that has three required fields that I need the user to fill in or select before they are allowed to proceed to other fields. These are all unbound fields.

    The first required field is a multi-select listbox, the second is an option group, and the third is a text box.

    The following routine is what I have so far, but it doesn't always work. I am able to go onto other fields even if the user has not chosen a value for the ABC Code parameter. Please refer to the attached screen shot which I think will make it clearer.

    Any help would certainly be appreciated.

    Code:
    Public Function ValidRequiredData() As Boolean
    ' this function will return TRUE if all the validation criteria is met
    On Error GoTo Err_ERROR
        
    ValidRequiredData = True
              
    '**********************************************************
    ' user has not chosen and dealer locations of dealer groups
    '**********************************************************
    If Forms!frm_Main!lstDlrLoc.ItemsSelected.count = 0 And Forms!frm_Main!lstDlrGrp.ItemsSelected.count = 0 Then
        
        MsgBox ("You must enter either Dealer Location(s) or Dealer Group(s)"), vbInformation, "Dealer Location/Group Validation Error"
        
        Call ClearAllTabs
        
        ValidRequiredData = False
        
        Exit Function
    End If
    
        
    '**********************************************************
    ' user has not entered a value for DaysOfSupply
    '**********************************************************
    If IsNull(Forms!frm_Main!txtDaysOfSupply.Value) Or Forms!frm_Main!txtDaysOfSupply.Value = "" Then   ' Null or blank
        
        Forms!frm_Main!txtDaysOfSupply.BackColor = 65535   ' set background to yellow
        MsgBox ("You have left the Days of Supply parameter blank. It is required."), vbInformation, "Days of Supply Validation Error"
        Forms!frm_Main!txtDaysOfSupply.SetFocus
        
        Call ClearAllTabs
    
        ValidRequiredData = False
        
        Exit Function
    End If
     
    '***************************************************************************************
    ' user has entered a value for DaysOfSupply that is non-numeric
    '***************************************************************************************
    Dim numeric As Boolean
    
    numeric = IsNumeric(Forms!frm_Main!txtDaysOfSupply)
    
    If Not (numeric) Then
    
        Forms!frm_Main!txtDaysOfSupply.BackColor = 65535   ' set background to yellow
        MsgBox ("Days of Supply must be a number."), vbInformation, "Days of Supply Validation Error"
        Forms!frm_Main!txtDaysOfSupply.SetFocus
        Forms!frm_Main!txtDaysOfSupply.Value = Null
        
        Call ClearAllTabs
        
        ValidRequiredData = False
        
        Exit Function
        
    End If
     
    '*******************************************************************************************
    ' User has entered a value for DaysOfSupply < 15 or > 500 or not evenly divisible by 15.
    ' We have to do this check after the above numeric check, otherwise we get a data mismatch
    ' error when checking the value and using the MOD function
    '*******************************************************************************************
    If Forms!frm_Main!txtDaysOfSupply.Value < 15 Or Forms!frm_Main!txtDaysOfSupply.Value > 500 Or _
       Forms!frm_Main!txtDaysOfSupply.Value Mod 15 <> 0 Then
    
        Forms!frm_Main!txtDaysOfSupply.BackColor = 65535   ' set background to yellow
        MsgBox ("Days of Supply must be between 15 and 500, and evenly divisible by 15"), vbInformation, "Days of Supply Validation Error"
        Forms!frm_Main!txtDaysOfSupply.SetFocus
        Forms!frm_Main!txtDaysOfSupply.Value = Null
           
        Call ClearAllTabs
        
        ValidRequiredData = False
        
        Exit Function
     
    End If
    
    '**********************************
    ' User has not chosen an ABC Code
    '**********************************
    If IsNull(Forms!frm_Main!fraABCcode) Then
        
        MsgBox ("You must enter the ABC Code"), vbInformation, "ABC Code Validation Error"
        
        Call ClearAllTabs
        
        ValidRequiredData = False
        'MsgBox (ValidRequiredData)
        Exit Function
    
    End If
       
    '***********************************************************
    ' validate user has chosen at least one parameter to change
    '***********************************************************
    Select Case Forms!frmLogin!category_frame
    
    Case Is = 1
    
        If Forms!frm_Main!opt_Cat1_PromoCode.Value = False And _
            Forms!frm_Main!opt_Cat1_StockClass.Value = False And _
            Forms!frm_Main!opt_Cat1_DistChannel.Value = False And _
            Forms!frm_Main!opt_Cat1_DlrSuppCode.Value = False And _
            Forms!frm_Main!opt_Cat1_AltDlrSuppCode.Value = False And _
            Forms!frm_Main!opt_Cat1_MinName.Value = False Then
    
            MsgBox ("You must enter at least 1 parameter to change"), vbInformation, "Parameter Choice Error"
        
            Call ClearAllTabs
        
            ValidRequiredData = False
            
            Exit Function
        
        End If
         
    Case Is = 2
    
        If Forms!frm_Main!opt_Cat2_Item.Value = False And _
            Forms!frm_Main!opt_Cat2_DistChannel.Value = False Then
    
            MsgBox ("You must enter at least 1 parameter to change"), vbInformation, "Parameter Choice Error"
        
            Call ClearAllTabs
        
            ValidRequiredData = False
            
            Exit Function
        
        End If
         
    Case Is = 3
    
        If Forms!frm_Main!opt_Cat3_TargetAmt.Value = False And _
            Forms!frm_Main!opt_Cat3_DistChannel.Value = False And _
            Forms!frm_Main!opt_Cat3_StockClass.Value = False Then
    
            MsgBox ("You must enter at least 1 parameter to change"), vbInformation, "Parameter Choice Error"
        
            Call ClearAllTabs
        
            ValidRequiredData = False
            
            Exit Function
        
        End If
         
    End Select
    
    Exit_function:                       ' Label to resume after error.
        Exit Function                    ' Exit before error handler.
    
    Err_ERROR:                           ' Label to jump to on error.
        Call LogError(Err.Number, Err.Description, "Function ValidRequiredData()")
        Resume Exit_function
              
    End Function
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    How are you calling this function ?

    Chris

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Like this...

    Code:
    Private Sub opt_Cat1_PromoCode_Click()
    On Error GoTo Err_ERROR
    
    Forms!frm_Main!txtDaysOfSupply.BackColor = RGB(255, 255, 255)  'white
    
    If Forms!frm_Main!opt_Cat1_PromoCode.Value = True And ValidRequiredData = True Then
    
        Forms!frm_Main!lst_Cat1_PromoCode.Enabled = True
    
    Else
        
        Forms!frm_Main!lst_Cat1_PromoCode.Requery
        Forms!frm_Main!lst_Cat1_PromoCode.Enabled = False
    
    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 opt_Cat1_PromoCode_Click()", Me.Name)
        Resume Exit_sub
    
    End Sub

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    As far as I see here, the only time your validation code will execute is when you click on the PromoCode option button. Does it work in this instance ? Any can't see any obvious problem with the code.

    There's nothing here to stop you clicking another option e.g. distribution channel option button. No validation will take place in this instance (unless you have more validations calls for each option). Is this what you are experiencing ?

    If you are still having difficulties then you need to try and narrow down when the validation works and when it doesn't. Maybe you should stil a temporary message box in the validation function to alert you that validation is taking placce. Then you can at least test that validation is being performed when you expect (even if it may not be returning the right result) i.e. when you click on Distribution Channel, are your expecting validation ?

    Chris

Posting Permissions

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