Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Greater than previous Entries (2003/SP1)

    I have a table with 3 fields, EquipmentID, Hours & Date. It will be used to keep track of the number of hours a piece of equipment, specifically forklifts, have been operated. The information will be gathered using a form. When the hours are entered, they have to be greater than or equal to the current highest total for that piece of equipment.
    How would I go about validating each entry? I could do it either at the table level, preferred, or on the form. I'm just not sure how I find the highest total for each piece of equipment, then make sure that the new entry is either greater than or equal. Thanks for the help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater than previous Entries (2003/SP1)

    I'd do this in the Before Update event of the control bound to the Hours field, or of the form. I don't think you can do this at the table level. The code would look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim varMax As Variant
    If IsNull(Me.Hours) Then
    ' Issue a warning
    MsgBox "Please enter the hours.", vbExclamation
    Me.Hours.SetFocus
    Cancel = True
    Else
    varHours = DMax("Hours", "tblSomething", "EquipmentID = " & Me.EquipmentID)
    If Not IsNull(varHours) Then
    If Me.Hours < varHours Then
    MsgBox "Hours must be at least " & varHours, vbExclamation
    Me.Hours.SetFocus
    Cancel = True
    End If
    End If
    End If
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Greater than previous Entries (2003/SP1)

    Hans,
    Thanks for the prompt response. For some reason it hangs up on this line (I changed it to reflect the actual name of the table, everything else matches the field names that I used):
    varHours = DMax("Hours", "tblHourMeter", "EquipmentID = " & Me.EquipmentID)
    Any idea what might be causing it to stop. I tried adding it both to the Before Update event of the Form and to the Hours field, hung up in the same spot with both. Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater than previous Entries (2003/SP1)

    I would have helped if you told us what the error message says.

    What is the data type of the EquipmentID field? (Text, number, ...)

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Greater than previous Entries (2003/SP1)

    Sorry, didn't even think about it, here is what the error says:

    Run Time Error '2001'

    You cancelled the previous operation.

    And then there are three buttons - End Debug Help

    The EquipmentID is Text, for example, TR1, for TRuck number 1.

    Thanks again for the help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater than previous Entries (2003/SP1)

    Since EquipmentID is a text field, you must enclose the value in quotes:

    varHours = DMax("Hours", "tblSomething", "EquipmentID = " & Chr(34) & Me.EquipmentID & Chr(34))

    Chr(34) is the double quote <code>"</code>.

  7. #7
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greater than previous Entries (2003/SP1)

    Or use single quotes if you don't want to remember the magic number for double quotes.

    varHours = DMax("Hours", "tblSomething", "EquipmentID = '" & Me.EquipmentID & "'")

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater than previous Entries (2003/SP1)

    Yep. See <!post=Chr() function and quotes within strings (all),230516>Chr() function and quotes within strings (all)<!/post> for an overview of the possibilities.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Greater than previous Entries (2003/SP1)

    That worked great. Thanks so much for the help.

Posting Permissions

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