Last updated signature - Required field (1 Viewer)

[brharrii]

I'd like to be able to track who was the most recent person to make changes to a record through a form I have setup and when those changes were last made.

I am currently working with 2 fields:

[Updated by]

and

[date last updated]

The [Updated by] field is to be populated with the user's name any time he / she makes a change to a record. If the user forgets to update this field and attempts to move on to another record or close the current form, a warning message is displayed asking them to go back and update this field and the user is unable to move on until they do so.

The [date last updated] field should be similar to the [updated by] field except that it would be the corresponding date of the last change. If possible it would be neat if this field could automatically update any time a change is made.

The one twist to this whole thing is that there is a third field that needs to be excluded from this rule. [date last used] represents the last date we produced a given product and would not require an electronic date / signature from the user. In other words, if a user is simply updating the [date last used] field, they would not need to enter their name / date to show that they made changes to the record.

Thank you in advance for any help you can offer,

Bruce

 

[VilaRestal]

Excluding the twist:

You could blank the [Updated By] field in the Form_Dirty event. Then in the Form_BeforeUpdate event check it isn't blank and insist the user fills it in if it is or set the [date lasted updated] field to now if it's been filled in:

Private Sub Form_Dirty()
    Me.[Updated by] = ""
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.[Updated by] = "" Then
        MsgBox "Please complete the Updated by field"
        Cancel = True
        Me.[Updated by].SetFocus
    Else
        Me.[date last updated] = Now()
    End If
End Sub

And I'm thinking how to include the twist with that...

 

[VilaRestal]

OK, with the twist:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error Resume Next
    Dim bDirty As Boolean
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Dirty Then
            If ctl.Name <> "[date last used]" Then
                bDirty = True
                Exit For
            End If 
        End If
    Next ctl
    If bDirty Then
        If Me.[Updated by] = "" Then
            MsgBox "Please complete the Updated by field"
            Cancel = True
            Me.[Updated by].SetFocus
        Else
            Me.[date last updated] = Now()
        End If
    End If
End Sub

Should work I think

 

[brharrii]

Awesome, thank you! I'll play with it and let you know how it goes

 

[Jeanette Cunningham]

I use this code, and I have separate fields for date created and created by for new records.
That code goes in the Before Update event of the form.
If you want to only catch updates where a value in at least one control has changed, then you check for changes in each control and only run the code to save updated by and date last updated to suit.

'-------code for before update of form
If Me.NewRecord = True Then
Me!EnteredBy = MyUserName
Me!EnteredOn = Now
Else
Me![Updated by] = MyUserName
Me![date last updated] = Now
End If
'------end code for before update form

Below is code to put in a module and call from any form to check for real changes to data in controls in a form.

'-------------
Public Function RealChangeCheck(frm As Form) As Boolean
'check if ctl values are changed
On Error GoTo Err_Handler

Dim ctl As Access.control

For Each ctl In frm.Controls

Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox
'Debug.Print ctl.Name & ": " & ctl
If Not ctl.Locked = True And ctl.Visible = True Then
If frm.NewRecord = True Then
If Len(ctl.Value & vbNullString) > 0 Then
RealChangeCheck = True
DoEvents
Exit For
End If
Else
If ctl.Value <> ctl.OldValue Then
RealChangeCheck = True
DoEvents
Exit For
End If
End If
End If
End Select
DoEvents
Next ctl

Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler

End Function
'--------------------

A user can make a change in one control then undo the change.
Access will assume the form is dirty and run the before update code, even though the net result was no change to any data.

 

[Jeanette Cunningham]

As well as looking for changes, you may want to work on tracking changes where the user deletes an entire record - if users can delete records in your database.

 

[brharrii]

OK, with the twist:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error Resume Next
    Dim bDirty As Boolean
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Dirty Then
            If ctl.Name <> "[date last used]" Then
                bDirty = True
                Exit For
            End If 
        End If
    Next ctl
    If bDirty Then
        If Me.[Updated by] = "" Then
            MsgBox "Please complete the Updated by field"
            Cancel = True
            Me.[Updated by].SetFocus
        Else
            Me.[date last updated] = Now()
        End If
    End If
End Sub

Should work I think

It set the time and date like a charm, but didn't queue the Msgbox and the exception didn't seem to work either.

 

[brharrii]

I use this code, and I have separate fields for date created and created by for new records.
That code goes in the Before Update event of the form.
If you want to only catch updates where a value in at least one control has changed, then you check for changes in each control and only run the code to save updated by and date last updated to suit.

'-------code for before update of form
If Me.NewRecord = True Then
Me!EnteredBy = MyUserName
Me!EnteredOn = Now
Else
Me![Updated by] = MyUserName
Me![date last updated] = Now
End If
'------end code for before update form

Below is code to put in a module and call from any form to check for real changes to data in controls in a form.

'-------------
Public Function RealChangeCheck(frm As Form) As Boolean
'check if ctl values are changed
On Error GoTo Err_Handler

Dim ctl As Access.control

For Each ctl In frm.Controls

Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox
'Debug.Print ctl.Name & ": " & ctl
If Not ctl.Locked = True And ctl.Visible = True Then
If frm.NewRecord = True Then
If Len(ctl.Value & vbNullString) > 0 Then
RealChangeCheck = True
DoEvents
Exit For
End If
Else
If ctl.Value <> ctl.OldValue Then
RealChangeCheck = True
DoEvents
Exit For
End If
End If
End If
End Select
DoEvents
Next ctl

Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler

End Function
'--------------------

A user can make a change in one control then undo the change.
Access will assume the form is dirty and run the before update code, even though the net result was no change to any data.

I'll play around with this one, I'm relatively novice to to access, but this should help a lot, thank you

 

[VilaRestal]

Did you have

Private Sub Form_Dirty()
Me.[Updated by] = ""
End Sub

too?

and try changing

If ctl.Dirty Then

to

If ctl.Value <> ctl.OldValue Then

 

[brharrii]

Oh I missed that part,

Now that i've added it I have something that looks like this:



Private Sub Form_Dirty()
Me.[Updated by] = ""
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
Dim bDirty As Boolean
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Dirty Then
If ctl.Name <> "[date last used]" Then
bDirty = True
Exit For
End If
End If
Next ctl
If bDirty Then
If Me.[Updated by] = "" Then
MsgBox "Please complete the Updated by field"
Cancel = True
Me.[Updated by].SetFocus
Else
Me.[date last updated] = Now()
End If
End If
End Sub



But now I'm getting an error message box as soon as I change view back to Form view. It says:


The expression on current you entered as the event property setting produced the following error: Prodcedure declaration does not match description of event or procedure having the same name.

I'll try playing with it a little bit more after I get out of this meeting I'm headed to. if you have any other ideas they are always welcome as well.

Thank you again for your assistance

 

[VilaRestal]

On current !? That's not those events. Is anything being changed in the current event that might trigger the dirty event?

Try saving the form in design, closing it and the opening it. Switching from design view to form view might be causing the problem.

And do change

If ctl.Dirty Then

to

If ctl.Value <> ctl.OldValue Then

too