Log when a record was last updated.

[Chyld]

I have a table with relational fields and one table inparticular has a section that I am currently having trouble with..

The fields are as follows:

Table: UpdateCheck

Fields: 'NameOfUserUpdating' 'DateUpdated' 'LastUpdated'

NameOfUserUpdating will log the system name of the user editing the record.

DateUpdated will log the date that the record is updated

LastUpdated will log the date that the record was updated previously (allowing backups to be found quicker.)

The problem I face is that I want the 'save' button to validate the record first to make sure that all the required info is present and then compare the old record against the new. If there have been no changes then the user will be informed that no changes have been made. If changes have been made, then the date that the record was altered will be logged in the 'DateUpdated' field BUT the date that was there previously will be transferred into the 'LastUpdated' field.

How do I do this?

 

[Databaseguy]

This is how I've done it in the past. You'll have to change the feild names etc

Public Function CheckText(frm As Form)
'Loops throught all trext boxes on a from passed in and checks for blanks and zeros
'Sets text box woth the exceptions background color to red
'Returns a count of exceptions to calling routine
Dim ctl As Control
Dim NumBlank As Integer
NumBlank = 0
    ' Enumerate Controls collection.
    For Each ctl In frm.Controls
        ' Check to see if control is text box.
        If ctl.ControlType = acTextBox Then
           ' Set control properties.
           With ctl
           'I make some controls invisible on the form, and you can't set the
           'focus on an invisible control, so....
           If ctl.Visible = True Then
            .SetFocus
                'I used the tag propoerty of the control. "A" designates a required field
                If .Tag = "A" Then
                    If .Text = "" Then
                        'NumBlank is passed back. If it's 0, there were no blanks, but, we
                        'just increased it by one
                        NumBlank = NumBlank + 1
                        'and made the text box background red
                        ctl.BackColor = RGB(255, 0, 0)
                        Else
                        ctl.BackColor = RGB(255, 255, 255)
                    End If
                Else
                End If
                'Tag "B" is same as "A", but I'm looking for a value greater than zero
                If .Tag = "B" Then
                    If .Text = "0" Then
                        'NumBlank is passed back. If it's 0, there were no blanks, but, we
                        'just increased it by one
                        NumBlank = NumBlank + 1
                        ctl.BackColor = RGB(255, 0, 0)
                        'and made the text box background red
                        Else
                        ctl.BackColor = RGB(255, 255, 255)
                    End If
                Else
                End If
            Else
            End If
           End With
         End If
         Next ctl
'Return number of exceptions to calling routine
CheckText = NumBlank
End Function

Usage: If checktext(me)> 0 then
'you've got a changed record
Else
'ya don't
End if

Other people use the isDirty property and/or recordset clones...