Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: validation rule for End date

    I have two "date/time" fields in my form. I use the pop up calendar to enter the StartDate and EndDate. They are working fine. That is I only have one calendar in the form and all the "date/time" fields in my form will call up the same calendar. The calendar will put the choosen date in the box.

    Both fields are a combo box because it would get the user to click to box to make the calendar visible.

    I want to make sure the End Date is either on or in the future compare to the Start Date.

    Here is my code and it is not doing anything right now. Can anyone tell me what have I done wrong. Thanks

    The combo box's name for the StartDate is ccfStartDate and the combo box's name for the EndDate is ccfEndDate

    Thank you much

    ******************************************

    Private Sub ccfEndDate_BeforeUpdate(Cancel As Integer)
    If Me.EndDate < Me.StartDate Then
    MsgBox "End Dates older then Start Dates not allowed"
    Cancel = True
    Exit Sub
    End If
    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggst you use a textbox instead of a combo... Combo suggests multiple option values, wheras a textbox can contain just the one!. Textboxes have click evnts too you know

    To test to see if an event is firing - simply pop a messagebox in the event, and nothing more.
    Code:
    Private Sub ccfEndDate_BeforeUpdate(Cancel As Integer)
      MsgBox "BeforeUpdate event fired for ccfEndDate"
    End Sub
    Have a play with different evens to see which one fires after you change the value in your textbox..
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Georgev, you are right, it didn't fire up on my EndDate but it does fire up in other places..

    Here is my code for the calendar and I think it has something to do with the way the code was written. Can you see if there should be other place to run the If condition? Thanks

    *************************************************
    Dim ccfOriginator As ComboBox

    **************************************************
    Private Sub ccfStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ' Note which combo box called the calendar
    Set ccfOriginator = ccfStartDate
    ' Unhide the calendar and give it the focus
    ccfCalendar.Visible = True
    ccfCalendar.SetFocus
    ' Match calendar date to existing date if present or today's date
    If Not IsNull(ccfOriginator) Then
    ccfCalendar.Value = ccfOriginator.Value
    Else
    ccfCalendar.Value = Date
    End If
    End Sub
    *************************************************
    Private Sub ccfEndDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ' Note which combo box called the calendar
    Set ccfOriginator = ccfEndDate
    ' Unhide the calendar and give it the focus
    ccfCalendar.Visible = True
    ccfCalendar.SetFocus
    ' Match calendar date to existing date if present or today's date
    If Not IsNull(ccfOriginator) Then
    ccfCalendar.Value = ccfOriginator.Value
    Else
    ccfCalendar.Value = Date
    End If
    End Sub

    *********************************************
    Private Sub ccfCalendar_Click()
    ' Copy chosen date from calendar to originating combo box
    ccfOriginator.Value = ccfCalendar.Value
    ' Return the focus to the combo box and hide the calendar and
    ccfOriginator.SetFocus
    ccfCalendar.Visible = False
    ' Empty the variable
    Set ccfOriginator = Nothing
    End Sub


    Thanks

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sweetmail, there's some calendar examples in the Access code bank you may want to take a look at. Not to say the method you're using is wrong or anything but looking at the calendar examples can give you some insight on different approaches which may help you with your coding.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's hard to tell anything from the above method other than the fact you're using comboboxes when a textbox would suffice (and be cleaner in the UI).

    Without knowing your calendar control then there's not much we can do to help you validate within the above code.
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2007
    Posts
    148
    I have changed my combo box to text box. I find out that the message will fire up under the Exit control or Change control. BUT not the before update or after update !!!

    Will look at the other examples.

    Thanks

  7. #7
    Join Date
    Sep 2007
    Posts
    148
    With many many try and error, I have resolved my problem. Here is the code

    Private Sub ccfEndDate_GotFocus()
    If Me.EndDate < Me.StartDate Then
    MsgBox "End Date Older then Start Date not allow"
    ccfEndDate = Me.StartDate
    Exit Sub
    End If
    End Sub

    Do I know why the got focus work for me. Absolutely not but it works!!! If anyone can explain to me why that would be great!!!

    Now, move onto the next probelm!!!

    Thanks
    Last edited by sweetmail; 10-17-07 at 16:11.

Posting Permissions

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