Results 1 to 8 of 8

Thread: Error Message

  1. #1
    Join Date
    May 2014
    Posts
    40
    Provided Answers: 2

    Answered: Error Message

    I have a form that documents events. I have one text box that records a start time and a second text box that records a end time. If the interval between start and end is greater than 20 minutes the director needs to be notified and the time of notification recorded in a 3rd text box.

    The question is how do I create an error message if the time interval is greater than 20 minutes and no time is recorded in the 3rd text box?

    I have placed a command button with this code,

    Code:
    If Forms!FormName![StartTime].Value - [EndTime].Value > "00:20:00" And [TBNotification] = "" Then
            
            MsgBox "What Time did Director Authorize?"
                
            
        End If
    DoCmd.Close acForm, "FormName"
    It runs through the code and closes the form no matter the time interval.

  2. Best Answer
    Posted by weejas

    "Hmm. You might have more luck with some variables and constants.
    Code:
    Dim datInterval As Date
    Const MININT AS Date = #00:20:00#
    
    datInterval = [EndTime].Value - [StartTime].Value
    
    If datInterval > MININT And Nz([TBNotification], "") = "" Then
    
    MsgBox "What time did the director authorise this?"
    [TBNotification].SetFocus
    
    Else
    
    Me.Close
    
    End If
    Also, it'll be easier to see values when stepping through the code."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    If you subtract the end time from the start time, you'll get a negative number.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    May 2014
    Posts
    40
    Provided Answers: 2
    Of course it does. made the change to end - start and still runs the code and closes the form.

    If I remove the 2nd part of the statement

    Code:
    And [TBNotification] = ""
    The message box opens even if the interval is less than 20 min.

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Hmm. You might have more luck with some variables and constants.
    Code:
    Dim datInterval As Date
    Const MININT AS Date = #00:20:00#
    
    datInterval = [EndTime].Value - [StartTime].Value
    
    If datInterval > MININT And Nz([TBNotification], "") = "" Then
    
    MsgBox "What time did the director authorise this?"
    [TBNotification].SetFocus
    
    Else
    
    Me.Close
    
    End If
    Also, it'll be easier to see values when stepping through the code.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    May 2014
    Posts
    40
    Provided Answers: 2
    So, I pasted weejas' code then it auto changes #00:20:00# to #12:20:00 AM#

    I change it and it auto goes back. What it up?

  7. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Ah, this is a "feature" of VBA that I'd forgotten. It treats a date/time value of 20 minutes as being 20 past midnight, and reformats the input data accordingly. Let it do what it wants, and give it a try.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  8. #7
    Join Date
    May 2014
    Posts
    40
    Provided Answers: 2
    Thanks weejas, works great!!!

  9. #8
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    You're welcome! Glad it's working for you!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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