Results 1 to 12 of 12

Thread: Validation

  1. #1
    Join Date
    May 2004
    Posts
    63

    Unanswered: Validation

    I have wrote this code to do validation for the "OrderDate" and "OrderRecvdDate". This code wrks fine when i have invalid data but then if its invalid it doesn't let me click on "cmdCalander" where user can pick diff date.

    Private Sub DateReceived_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.DateReceived.Value) Then
    MsgBox "You cannot leave the Received Date field blank and cannot be before OrderDate", vbInformation
    Cancel = True
    Exit Sub
    ElseIf Me.DateReceived.Text < Me.OrderDate Then
    MsgBox "Date Received cannot be before DateOrder" & vbCrLf
    Cancel = True
    End If

    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    one-way dead end street

    You have created a "one-way dead end street".

    To click the calendar control, you have to leave the date field. Trying to leave the date field triggers the Before_Update event, which prevents the focus from shifting to the calendar control if the date is invalid.

    Thus, to click on something else, you need to enter a value in the date field.

    One easy solution is to not allow the user to enter in a date - force them to use the calendar. Most uses won't mind - but your data entry people will hate you.

    Another easy solution is to provide a default date in the event the date is invalid.

    And of course, there are many many other ways of handling this scenerio.

    Have Fun!

  3. #3
    Join Date
    May 2004
    Posts
    63
    Do you have any example of how to handle this in different way ?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    To eliminate the trap, use and After_Update instead. This will provide the initial warning to the user, but allow them to move elsewhere on the form.

    Are you using bound controls? If so, and your DB can not handle a null value in this field, you'll have to put some sort of catch somewhere else before the record is updated.

    You may want to hide the Date control and replace it with an unbound control. Most simply, you can use a default date in the date control, and in the unbound control's After_Update, copy the date into the bound control if it is valid. When the calendar is clicked, copy the date into the bound and unbound fields.

    If using unbound controls, put the check in before the update.

    The specifics of how to order the operations depends on how the form is setup. Can you add a zipped mdb with just the form?

  5. #5
    Join Date
    May 2004
    Posts
    63
    I have attached the zip mdb
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Piece of cake!

    Since you are already using custom navigation controls:
    1) Change the date check to After_Update (remove the Cancel = True statements) and replace them with a DoCmd.GotoControl("OrderDate") . This notifies the user they made a mistake and keeps the focus on the date control. Since it's "After_Update" the user is free to click the calendar.
    2) Add in the date validation to each record navigation button and do not allow the user to change records (therefore updating the record) until they fix it.

    To be more tidy in your code, add a function at the bottom of the module:
    Code:
    Function ValidReceivedDate() As Boolean
    
        Dim blVal As Boolean
        
        blVal = False
        If IsNull(Me.DateReceived.Value) Then
            MsgBox "You cannot leave the Received Date field blank and cannot be before Date onset", vbInformation
            DoCmd.GoToControl ("DateReceived")
        ElseIf Me.DateReceived.Text < Me.DateOnSet Then
            MsgBox "Date Received cannot be before Date onset" & vbCrLf
            DoCmd.GoToControl ("DateReceived")
        Else
            blVal = True
        End If
        ValidReceivedDate = blVal
    End Function
    In your afterupdate and in each navigation button, add this line:
    Code:
    If Not ValidReceivedDate Then Exit Sub
    Adding the function to each navigation button prevents the user from ignoring the message and saving the record.

    Enjoy

  7. #7
    Join Date
    May 2004
    Posts
    63
    I have put this code for AfterUpdate but still it doesn't wrk right when Received date is < Order date. It wrks fine when ther is no date input for Received date

    Code:
    Private Sub DateReceived_AfterUpdate()
    
    If IsNull(Me.DateReceived.Value) Then
        MsgBox "You cannot leave the Received Date field blank and cannot be   
                    before Date onset", vbInformation
        DoCmd.GoToControl ("DateReceived")
        Exit Sub
    ElseIf Me.DateReceived.Text <= Me.DateOnSet Then
        MsgBox "Date Received cannot be before Date onset" & vbCrLf
        Cancel = True
    End If
    
    End Sub

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would say take out the " & vbCrLf" see what happens then

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    May 2004
    Posts
    63
    I remove " & vbCrLf" but its still same problem. Basically, field gets validated when its "Null".... but nothing happens when DateReceived is < then DateOrder.

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Try removing the If on the Else.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Try using the function I povided.

  12. #12
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    tcace
    Registered User Join Date: Apr 2004
    Posts: 111


    Try using the function I povided.
    Sorry I thought he was already using yours... Yea I think yours will work.

    Hey SAK2004 try out tace's example.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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