How to validate data for new and past records being entered

[DawnTreader]

Hello All

I have a problem where I need to figure out how to know a record that is
before and another record that is after a new record that I am about to
create.

For instance, I have a table where I store information of "running hour"
history. The machine runs a number of hours and on a date I record the
current amount of total hours the machine has run. At a later date I record
the hours again, always greater than the last time I recorded the hours, but
there is no predetermined schedule for recording the hours. So now a coworker
has a record that I want to place in between the 2 dates but I need to make
sure that the entry will be valid. Here is what it looks like:

RHDate RunningHoursID HoursAtDate ServiceReportID BlockID
2009/07/04 21152 178 22466
1674
2009/06/28 21084 117 22399
1674
2009/06/24 21085 84 22400
1674
2009/06/22 20871 67 22161
1674
2009/06/21 20875 67 21043
1674
2009/06/21 20869 60 22159
1674
2009/06/19 20807 39 22094
1674
2009/06/17 20806 17 22093
1674
2009/06/09 20508 7 21796
1674

Some things to note about the data, the running hour date is the same as
entered on the service report. The blockID is the machine ID, so all this is
for one machine.

Now let’s say that someone recorded a service report on 2009/06/26 and was
entering it. How do I determine if the entry of hours will be valid? No
machine can run more than 24 hours a day, and it has to be more than the
hours recorded on the 24th and less that the hours on the 28th. I created a
query to give me a list of all the hours, showing the prior hours and date,
and then it calculates a lot of what I am talking about, but how do I get a
form to do the check using the same logic?

I need a way for the form to check against something to see if the hours
work into the logic, but I don’t know if I should be creating a query, or a
routine or function on my form.

When someone enters the running hours on the service report I need the form
to check the date of the report and the hours the user entered against the
previous data and figure out if the amount of hours are valid and with in the
range of hours already entered. So…

RHDate RunningHoursID HoursAtDate ServiceReportID BlockID
2009/06/28 21084 117 22399
1674
2009/06/26 22085 96 25050
1674
2009/06/24 21085 84 22400
1674

Is good data…

RHDate RunningHoursID HoursAtDate ServiceReportID BlockID
2009/06/28 21084 117 22399
1674
2009/06/25 22085 109 25050
1674
2009/06/24 21085 84 22400
1674

Is bad data, because the difference between 84 and 109 is 25, 1 hour more
than it could run in a day.

RHDate RunningHoursID HoursAtDate ServiceReportID BlockID
2009/06/28 21084 117 22399
1674
2009/06/27 22085 146 25050
1674
2009/06/24 21085 84 22400
1674

Is greater than the next date’s entry.

I hope I have explained the situation well enough at this point. I am hoping
someone can give me some ideas as to what to do to validate these entries on
this form.

I also apologize for the long post, just hope it all makes sense.

As always, thanks for any and all help.

 

[Beetle]

This is air code and could probably use some tweaking,
but it may get you started. You'll need to modify with
your own field & table names, etc.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim PrevDate As Date
Dim NextDate As Date
Dim PrevHours As Long
Dim NextHours As Long


PrevDate = Nz(DMax("RHDate", "tblRunningHours", "RHDate<#" _
& Me.RHDate & "# And BlockID=" & Me.BlockID), 0)

NextDate = Nz(DMin("RHDate", "tblRunningHours", "RHDate>#" _
& Me.RHDate & "# And BlockID=" & Me.BlockID), 0)

PrevHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _
& PrevDate & "# And BlockID=" & Me.BlockID), 0)

NextHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _
& NextDate & "# And BlockID=" & Me.BlockID), 0)

If PrevHours > 0 Then
If Me.Hours < PrevHours Then
MsgBox "Hours value must be greater than the previous record."
Cancel = True
ElseIf (Me.Hours - PrevHours) > _
(DateDiff("d", PrevDate, Me.RHDate) * 24) Then
MsgBox "Not that many hours in the time frame"
Cancel = True
End If
End If

If NextHours > 0 Then
If Me.Hours > NextHours Then
MsgBox "Hours value must be less than the next record."
Cancel = True
End If
End If

End Sub

 

[DawnTreader]

Hello Beetle

hmmm... that could work. thanks for the insight.

sometimes walking away from a problem and getting a second pair of eyes on
it can really help.