Looking up a different field in a previous record

[Dugantrain]

Here's a pretty complex problem that has me stumped:
I have a hardware tracking database. I have a main form with a datasheet subform. The main form has information on the Site that was installed, the Tech who did the install, and the Kit that he used. The subdatasheet has a listing of items in the Kit, how many the Kit started with (On-Hand), how many were Picked Up at the site, how many were Dropped Off, and how many were left when the Tech departed (New On-Hand). The problem is that the Techs aren't always accurately reporting their starting stocks; for each item, the On-Hand should match exactly the New On-Hand for the last install. So what I want the db to do is this:

On-Hand_after_Update
If this On-Hand number does not match exactly the New On-Hand number for the last record of this item being in this Kit(if there is a previous record, which there may not be), then msgbox "This Kit's me.item On-Hand stock of me.on_hand does not match its last New On-Hand stock of (previous_install_record_for_this_item.new_on_hand)."
End Sub

I've been wrestling around with this one for a while and have come up with nothing.

 

[WayneRyan]

DuganTrain,

' *************************************
Dim dbs As DataBase
Dim rst As RecordSet
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from KitHistory " & _
"Where KitName = '" & Me.KitName & " And " & _
"KitDate < #" & Me.KitDate & "# " & _
"Order by KitDate Desc"
Set rst = dbs.OpenRecordSet(sql)
If rst.EOF and rst.BOF Then
' No prior, do nothing
Exit Sub
End If
rst.MoveFirst
If rst!OnHand <> Me.OnHand Then
MsgBox("Wrong starting #")
End If
' *************************************

I haven't tried this code, but its the general idea.
You could also use a DLookUp with the same type of
criteria as in the sql string.

btw, You really shouldn't store data in multiple places,
especially when you can calculate it (current on-hand)
because it leads to problems like this.

hth,
Wayne

 

[Dugantrain]

Yes, your SQL statement is the general idea that I'm looking for, I'll get to work with this idea. Unfortunately, the Project Managers want every number in the database archived and they're actually trying to catch Techs when they report discrepancies. Since we're trying to compare two instances of the same data, recording it twice seems appropriate.