Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    8

    Unanswered: Learning Validation Rules for my form

    My company has a maintenance contract for all of its equipment with a local maintenance provider. Each day equipment operators enter specific data related to their equipment into the access database form. In order to reduce errors, I have the most recent data pulled from previous entries using the following two queries:

    1. I have the following SQL in my form to pull the most recent data from the query below when each piece of equipment is selected from a dropdown menu.

    SELECT [Tbl Equipment].[Equipment ID], [Tbl Equipment].EquipmentNumber, [Tbl Equipment].EquipmentType, [Tbl Equipment].[FuelType ID], [Qry Max Date Status info].[Status HourReading], [Qry Max Date Status info].EmployeeID, [Qry Max Date Status info].[Building ID], [Qry Max Date Status info].[Maintenance Required], [Qry Max Date Status info].[Maintenance Comments]
    FROM [Tbl Equipment]

    LEFT JOIN [Qry Max Date Status info]

    ON [Tbl Equipment].[Equipment ID] = [Qry Max Date Status info].[Equipment ID];

    =====================================
    2. This query pulls the most recent data for each piece of equipment in the database

    SELECT [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]
    FROM [Tbl Status]

    LEFT JOIN [Tbl Status] AS Self ON [Tbl Status].[Equipment ID]=Self.[Equipment ID]

    GROUP BY [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]

    HAVING ((([Tbl Status].[Status Date])=Max([Self].[Status Date])));




    What I would like to do now, is put a validation rule in the form that would prevent employees from entering a [Status HourReading] that is less than the one previously entered. To be honest, I am very unfamiliar with validation rules, I noticed that they were available when I selected properties after right-clicking on the field when I had the form in design view. Can anyone please help me out? Thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Learning Validation Rules for my form

    Originally posted by ClarkRobJ
    My company has a maintenance contract for all of its equipment with a local maintenance provider. Each day equipment operators enter specific data related to their equipment into the access database form. In order to reduce errors, I have the most recent data pulled from previous entries using the following two queries:

    1. I have the following SQL in my form to pull the most recent data from the query below when each piece of equipment is selected from a dropdown menu.

    SELECT [Tbl Equipment].[Equipment ID], [Tbl Equipment].EquipmentNumber, [Tbl Equipment].EquipmentType, [Tbl Equipment].[FuelType ID], [Qry Max Date Status info].[Status HourReading], [Qry Max Date Status info].EmployeeID, [Qry Max Date Status info].[Building ID], [Qry Max Date Status info].[Maintenance Required], [Qry Max Date Status info].[Maintenance Comments]
    FROM [Tbl Equipment]

    LEFT JOIN [Qry Max Date Status info]

    ON [Tbl Equipment].[Equipment ID] = [Qry Max Date Status info].[Equipment ID];

    =====================================
    2. This query pulls the most recent data for each piece of equipment in the database

    SELECT [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]
    FROM [Tbl Status]

    LEFT JOIN [Tbl Status] AS Self ON [Tbl Status].[Equipment ID]=Self.[Equipment ID]

    GROUP BY [Tbl Status].[Equipment ID], [Tbl Status].[EmployeeID], [Tbl Status].[Building ID], [Tbl Status].[Status HourReading], [Tbl Status].[Maintenance Required], [Tbl Status].[Maintenance Comments], [Tbl Status].[Status Date]

    HAVING ((([Tbl Status].[Status Date])=Max([Self].[Status Date])));




    What I would like to do now, is put a validation rule in the form that would prevent employees from entering a [Status HourReading] that is less than the one previously entered. To be honest, I am very unfamiliar with validation rules, I noticed that they were available when I selected properties after right-clicking on the field when I had the form in design view. Can anyone please help me out? Thanks!

    The Validation Rules Property is a short cut method of the AfterUpdate event. You may want to just bite the bullet and use it rather than the Validation Rule setup.

    an example:

    sub EqID_afterUpdate()

    Dim db as database, rs as recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Query2")
    rs.findfirst "[Equipment ID] = " & Me!EqID & " and [Status HourReading] > " & Now()
    if rs.nomatch then
    msgbox "Entry is out of range"
    end if
    rs.close
    db.close
    set rs = nothing
    set db = nothing

    end sub

  3. #3
    Join Date
    Mar 2003
    Posts
    8
    I currently have the following code in the EquipmentID_AfterUpdate() event. If you'll notice, it is grabbing the information from query #1 and plugging it into the form.

    Private Sub Equipment_ID_AfterUpdate()
    Me![Status HourReading] = Me.Equipment_ID.Column(4)
    Me![EmployeeID] = Me.Equipment_ID.Column(5)
    Me![Building ID] = Me.Equipment_ID.Column(6)
    Me![Maintenance Required] = Me.Equipment_ID.Column(7)
    Me![Maintenance Comments] = Me.Equipment_ID.Column(8)

    If Me.Equipment_ID.Column(8) = "" Then
    Me!Option32.OptionValue = [Maintenance Required]
    Me!Option34.OptionValue = -1
    Me![Maintenance Comments].Enabled = False
    Else
    Me!Option34.OptionValue = [Maintenance Required]
    Me!Option32.OptionValue = 0
    Me![Maintenance Comments].Enabled = True
    End If

    End Sub


    After this fills in each of the fields in the form, the operator then updates the field accordingly. Most of the time, all that is needed is an adjustment to the [Status HourReading] field. The user then selects OK and a blank form is presented for the next operator to use. The following is the data for the OK button.

    Private Sub OK_Click()
    On Error GoTo Err_OK_Click
    Me![Maintenance Comments].Enabled = False
    DoCmd.GoToRecord , , acNewRec

    Exit_OK_Click:
    Exit Sub

    Err_OK_Click:
    MsgBox Err.Description
    Resume Exit_OK_Click

    End Sub


    I tried to enter the following code into the Sub OK_Click():

    If Me![Status HourReading] < Me.Equipment_ID.Column(4) Then
    MsgBox("Please Fix Hour Reading Entry!")
    Exit Sub
    End If

    However that didn't work. My guess is because I set the two equal in the EquipmentID_AfterUpdate() section of the code earlier. I then tried to enter the code that you suggested in there and got an error in the following section:
    Dim db as database, rs as recordset

    The word database was highlighted. Do you have any ideas for me?

  4. #4
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by ClarkRobJ
    I currently have the following code in the EquipmentID_AfterUpdate() event. If you'll notice, it is grabbing the information from query #1 and plugging it into the form.

    Private Sub Equipment_ID_AfterUpdate()
    Me![Status HourReading] = Me.Equipment_ID.Column(4)
    Me![EmployeeID] = Me.Equipment_ID.Column(5)
    Me![Building ID] = Me.Equipment_ID.Column(6)
    Me![Maintenance Required] = Me.Equipment_ID.Column(7)
    Me![Maintenance Comments] = Me.Equipment_ID.Column(8)

    If Me.Equipment_ID.Column(8) = "" Then
    Me!Option32.OptionValue = [Maintenance Required]
    Me!Option34.OptionValue = -1
    Me![Maintenance Comments].Enabled = False
    Else
    Me!Option34.OptionValue = [Maintenance Required]
    Me!Option32.OptionValue = 0
    Me![Maintenance Comments].Enabled = True
    End If

    End Sub


    After this fills in each of the fields in the form, the operator then updates the field accordingly. Most of the time, all that is needed is an adjustment to the [Status HourReading] field. The user then selects OK and a blank form is presented for the next operator to use. The following is the data for the OK button.

    Private Sub OK_Click()
    On Error GoTo Err_OK_Click
    Me![Maintenance Comments].Enabled = False
    DoCmd.GoToRecord , , acNewRec

    Exit_OK_Click:
    Exit Sub

    Err_OK_Click:
    MsgBox Err.Description
    Resume Exit_OK_Click

    End Sub


    I tried to enter the following code into the Sub OK_Click():

    If Me![Status HourReading] < Me.Equipment_ID.Column(4) Then
    MsgBox("Please Fix Hour Reading Entry!")
    Exit Sub
    End If

    However that didn't work. My guess is because I set the two equal in the EquipmentID_AfterUpdate() section of the code earlier. I then tried to enter the code that you suggested in there and got an error in the following section:
    Dim db as database, rs as recordset

    The word database was highlighted. Do you have any ideas for me?
    What I sent before may not be of value because you already have the Max value for [Tbl Status].[Status HourReading] which is Me.Equipment_ID.Column(4).

    I am not sure why you transfer the old data from ComboBox to Me![Status HourReading] when you seem to want a more current value (is that right?)

    Can't you calculate the date/time/value you need and avoid the dataentry person from having to enter anything?

    i.e.
    Me![Status HourReading] = Me.Equipment_ID.Column(4) + 1
    Me![Status HourReading] = now()
    Me![Status HourReading] = format(now(),"hh:mm AMPM")
    Me![Status HourReading] = dmax("[Status HourReading]","[Tbl Status]") +1

    The only thing seems odd with what you wrote is that Me![Status HourReading] can be the same value. To always test for an increase the value use <=

    If Me![Status HourReading] <= Me.Equipment_ID.Column(4) Then
    MsgBox("Please Fix Hour Reading Entry!")
    Me![Status HourReading] = Me![Status HourReading].oldvalue
    Exit Sub
    End If

  5. #5
    Join Date
    Mar 2003
    Posts
    8

    Wink

    The value entered in [Tbl Status].[Status HourReading] is more than just a simple addition of one. The equipment that this database is for is forklifts. The usage of the forklift each day is unknown. It could not be used at all, for 1,2,3,4,5,6,7,8 or even more hours if it needs to be used overtime. Some of our locations have several shifts as well so that could be an addition of 20 hours per day.

    I recall the old data currently because the operator, building do change but rarely. In order to minimize time spent entering this in (since we have around 400 machines) this information is recalled. Repairs to the equipment might not happen for several days but we may still use the equipment until maintenance comes to repair it. For example, the horn may be out, or the battery may not be fully charged. Therefore, we need to recall this information as well.

    I recall the hours so that employees can have something to compare todays entry to. They may have had 1500 hours on their forklift yesturday and quickly looked at it today and thought that they had 1589 hours today. However, when they go to enter today's Hour readings and see that there is an 89 hour difference in only one day, they can realize their error and enter 1509 instead.

    Lastly, a person may have had 1500 hour yesturday and only used the lift for 10 minutes today before they had to leave. According to our ISO 9000 standards, they have to enter this information in even though the hours did not change because they only used the lift for 1/6 of an hour. For that reason, it would be just fine to have the previous data equal the current data.

    Thanks

  6. #6
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by ClarkRobJ
    The value entered in [Tbl Status].[Status HourReading] is more than just a simple addition of one. The equipment that this database is for is forklifts. The usage of the forklift each day is unknown. It could not be used at all, for 1,2,3,4,5,6,7,8 or even more hours if it needs to be used overtime. Some of our locations have several shifts as well so that could be an addition of 20 hours per day.

    I recall the old data currently because the operator, building do change but rarely. In order to minimize time spent entering this in (since we have around 400 machines) this information is recalled. Repairs to the equipment might not happen for several days but we may still use the equipment until maintenance comes to repair it. For example, the horn may be out, or the battery may not be fully charged. Therefore, we need to recall this information as well.

    I recall the hours so that employees can have something to compare todays entry to. They may have had 1500 hours on their forklift yesturday and quickly looked at it today and thought that they had 1589 hours today. However, when they go to enter today's Hour readings and see that there is an 89 hour difference in only one day, they can realize their error and enter 1509 instead.

    Lastly, a person may have had 1500 hour yesturday and only used the lift for 10 minutes today before they had to leave. According to our ISO 9000 standards, they have to enter this information in even though the hours did not change because they only used the lift for 1/6 of an hour. For that reason, it would be just fine to have the previous data equal the current data.

    Thanks
    It is a concatenated coded field??? Example XXX0000ZZ where X is the equipment identifier, 0 is the operating hours and ZZ is its status. If it is fixed length format you can parse it into its components and rebuild it into its field format. For example, Equip ID is always the first 3 places, hours the next 4, etc. The hours can be tested with a simple comparison or range in the case of fat-finger keying, the status/repair can be more pulled from a pick list. Not sure what all the segments are, but you could have unlimited segments objects that you can control and all compiled into the final Status_HourReading field. True?

    Me!Status_HourReading = Right(Me!Equip_ID, 3) & Left(0000,4-Len(Me!Eq_HR) & Me!Eq_HR & - &Me!RepairStatus

    If it is not fixed length it is still possible but more problematic. Let me know.

Posting Permissions

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