Tracking number changes in fields of a form

[chasmith17]

Hello,
Can someone advise me on how to track changes in fields of a form. For example if I enter a value in a field on my form for one record and then the next time I enter a value in that same field on the same form but using a different record, how can I setup where I can either see what the value was on the previous record or maybe just flag when the value exceeds the previous record by a certain value. I hope I haven't confused you.

 

[rjoubert]

Are you talking about an audit trail? I.E. Keeping track of the former value of a field on a form.

 

[chasmith17]

Thanks for the reply rjoubert,
That sounds like what I am trying to do. Can you tell me how it works?

 

[MajP]

Describe in a little more detail how you want this to work. This does not seem like an audit trail to me because an audit trail usually keeps the history for a given record. This sounds simply like you just want visibility of the previous record. Here is a simple example of how to make the start date of the next record equal the end date of the previous

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord And IsDate(Me.dtmEndDate) Then
Me.dtmStartDate.DefaultValue = "#" & Me.dtmEndDate & "#"
End If
End Sub

 

[chasmith17]

MajP,
On my form I have Asset ID and some other test point fields that I record for Preventative Maintenance on an Asset. I would like for the field to flag me if the new value I enter in the field is let say 0.9 different than the previus record. Thanks for your help

 

[MajP]

What do you mean by previous record? I assume that it is the last preventive record entered for for a given asset. You need to be careful when talking about record order because there really is not a record order until the recordset is sorted. In this example I will assume that each preventive maintenance has a unique pmID (auto id) and an AssetID foriegn key. Because the pm's have an auto ID I can use this to find the last pm entered for a specific asset.

Private Sub dblField_BeforeUpdate(Cancel As Integer)
 Dim oldValue As Double
 Dim lastRecord As Integer
 'the record with the greatest auto id is the last entered
 'for an asset
 lastRecord = DMax("pmID", "tblPM", "assetID = " & Me.assetID)
 'use the pmID to find the value
 oldValue = DLookup("dblField", "tblPM", "pmID = " & lastRecord)
 If oldValue <= 0.9 * Me.dblField Then
   MsgBox "Value is .9 greater than last value"
 End If
End Sub

Depending on the fields that you have this could be done easier if you have some kind of unique field.

 

[chasmith17]

MajP,
Your assumption is right, it is the last pm record entered. You are also right in that each pm record has an ID. Will the above code send me to the last pm record entered or will it just flag me when a particular field is out of the tolerance? Also what do you mean by Unique field?

 

[MajP]

This example simply pops up a message box,

If oldValue <= 0.9 * Me.dblField Then
MsgBox "Value is .9 greater than last value"
End If

but you could do all kinds of things to "flag" the record.

By "unique field" I mean some field that allows me to sort the table so that the most current records appear at the end. Again, record order for a database is usually meaningless unless there is a field that you can sort it. I assumed that the pms had an auto id, but this may not necessarily be a good choice. If I get behing in my data input, and I input several days pms out of order then record order is meaningless so is my auto id. However, if I can sort on "date performed" I can pull up the most current date. If you post the fields that would help.

 

[chasmith17]

I can sort by Date. Your example you gave is just what I am looking for. Just curious, what other kinds of things can I do? There are way too many fields to post. Thanks alot for your help MajP.

 

[MajP]

So if each pm has a date assigned something like this would be better

Private Sub dblField_BeforeUpdate(Cancel As Integer)
Dim oldValue As Double
Dim lastPM As date
'the record with the greatest date is the last entered
'for an asset
lastPM = DMax("dtmPMDate", "tblPM", "assetID = " & Me.assetID)
'use the lastPM to find the value
oldValue = DLookup("dblField", "tblPM", "assetID = " & Me.assetID & " and dtmPMDate = #" lastPM & "#")
If oldValue <= 0.9 * Me.dblField Then
MsgBox "Value is .9 greater than last value"
End If
End Sub

You could do anything. Pop up and icon, lock out controls, change a controls color, font, background. Anything that you can design manually can be done in code.