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

    Unanswered: Data Validation Routine Help

    I am trying to validate user entries on a form. This form has some fields that are required.

    (Dealer Location - user must choose at least 1 from a multiselect list box called DlrListbox.

    OR

    Dealer Group - again, the user must choose at least 1 from a multiselect list box called DlrGroupListbox)

    AND

    Choose 1 value from an option group called ABC_code_frame

    AND

    User must enter a value in a text box called DaysOfSupplytext


    I am trying to write a simple routine that will check all these conditions and will not let the user proceed until they are all correctly filled out. I tried doing this myself but I get lost in the nesting of the if's.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This doesn't require too many if's

    Code:
    Function Validate_Data as Boolean
    
    Validate_Date=False
    If Not Isnull(DlrListBox) or Not IsNull(DlrGroupListBox) Then
        If Not IsNull(Abc_Code_Frame) Then
             If LEN(NZ(DaysOfSupplyText,"")>0 Then
                'all conditions are true
                Validate_Date=True
             End If
         End IF
    End IF
    
    Exit Function
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    That looks good, but I have a dumb question. How do I call it?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Judging by your question, I'm guessing your form is bound to the data source.
    That means the data being put in by the users is going directly to the table.
    That's fine if you have a "personal" database, and you know what is expected. It's not a good idea if you have "users", though.

    Are you using VBA in your app at all?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Yes, I am doing all of this in VBA, and no, the form is not bound. All the form does is take in values, and then once all the fields are completed, they press OK and the records get written to a table.

    I had to change your initial script a bit because it didn't appear to work correctly, but I think I messed it up. I probably should have given you the entire requirement. The only piece I left out is that the DaysOfSupply text box value must be between 15 and 500, and be divisible by 15.

    Code:
    Function Validate_Data() As Boolean
    
    Dim Data_ok As String
    Data_ok = False
    
    If (DlrListbox.ItemsSelected.Count <> 0) Or (DlrGroupListbox.ItemsSelected.Count <> 0) Then 'user selected 1 or more Location(s) or Dealer group(s)
        If Not IsNull(ABC_code_frame) Then  'user has selected an ABC code
            If Not ((Me.DaysOfSupplytext.Value >= 15 And Me.DaysOfSupplytext.Value <= 500) And ((Me.DaysOfSupplytext.Value) Mod 15 = 0)) Then
                MsgBox "Value Must Fall Between 15 and 500 and Be Evenly Divisible by 15", vbOKOnly, "Field Value Incorrect !"
                If Len(Nz(DaysOfSupplytext, "")) > 0 Then
                    'all conditions are true
                    Data_ok = True
                    MsgBox ("Required fields are OK. We will now insert records"), vbInformation, "Validation Success"
                End If
            End If
         End If
    Else
        MsgBox ("Required fields are not correct. Please fix"), vbInformation, "Validation Failure"
    End If
    
    End Function

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You would call the function from the sub you are using to save your data.
    Use something like:
    ...
    If Validate_Data=False then
    MsgBox ("Required fields are not correct. Please fix"),vbInformation, "Validation")
    Else
    continue saving data
    End If
    ...

    You need to do away with the data_ok variable, though. The function has
    to return the true/false value.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Sep 2005
    Posts
    220
    This is driving me nuts. I returned it to what you had. Now when I run my program, no matter what comination of options I choose, the Valid_data variable is always False, and my code to do the insert never gets run.

    DlrListbox has a record selected, the ABCcode option group has a button selected, and the DaysOfSupply text box has a value of 15.


    Code:
    If Not IsNull(DlrListbox) Or Not IsNull(DlrGroupListbox) Then
        If Not IsNull(ABC_code_frame) Then
            If Len(Nz(DaysOfSupplytext, "")) > 0 Then
                Valid_data = True   'all conditions are true
            End If
         End If
    End If

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I tested this on my own machine, just to make sure I wasn't missing something obvious. It works as expected.

    Have you tried "stepping" through the code to see which argurment is "failing".
    My guess is that it's the first one. Are you sure there is a value selected in
    the list box, and you're not just going with the default value that is diplayed.
    Just because the first value is displayed in the list box, if you don't select it,
    it will return Null
    Inspiration Through Fermentation

Posting Permissions

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