Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: Before Update/Validation Rule

    I am trying to get the following code to work and it is not. I have a database tracking monthly mileage of vehicles. I put this in the form's BeforeUpdate event. The code is doing a DLookup on a query that finds the last mileage entered for each vehicle. The new mileage has to be compared to the last mileage to make sure the new data is not less than and not more than 5,000. Any help is appreciated.

    Private Sub Mileage_BeforeUpdate(Cancel As Integer)

    Dim stMileage As Long

    stMileage = DLookup("[LastOfMileage]", "qry-LastMileage", "[UnitID] ='" & Me!UnitID & "'")

    If (stMileage > Me!Mileage) Then
    MsgBox "The mileage you entered is less than the last entered mileage. Please verify."
    Cancel = True
    Me!Mileage.Undo

    ElseIf (Me!Mileage > (stMileage + 5000)) Then
    MsgBox "The mileage you entered is more than 5,000 miles. Please verify."
    Cancel = True
    Me!Mileage.Undo

    End If

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so whats the problem?
    have you checked that your domain function is returning the right value

    incidentally you could consider shortcircuiting your logic

    Code:
    If (stMileage > Me!Mileage or Me!Mileage > (stMileage + 5000)) Then
      MsgBox "The mileage you entered is invalid (outside range last month.. lastmonth +5000 mile. Please verify."
      Cancel = True
    End If
    not sure you need to inseet the undo statement
    you can keep your style if you prefer, but effectively it doesn't matter either way
    incidentally are you checking ofr a null value
    you may need to explicilty convert the text box value to long using clng
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What exactly is the problem? Is the DLookup not working? Without your answers, my first impression is maybe UnitID is not a string. In your DLookup you are treating it like a string. Is this correct?

  4. #4
    Join Date
    Jul 2003
    Posts
    16
    The DLookup is working fine, the problem seems to be in the ElseIf statement. The If statement works by itself, but when I add in the ElseIf any number I enter returns the message that it is over 5000 miles.

  5. #5
    Join Date
    Jul 2003
    Posts
    16
    Thanks for the tip healdem I'll give that a try and see what happens.

Posting Permissions

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