how to stop last edit date changing when record just viewed or copied

[pgcn]

I want a textbox (DateChanged) on the subform (sfmStakeholder_Details)
to reflect the date the record was last changed. This subform has a
number of tabs and often I need to view the tabs and/or copy text (Ctrl
+C) without editing or changing DateChanged. There may also be a
number of records (Stakeholder_Details) for each frmStakeholder and I
don't want the date changing when they are passed using the navigation
record selector or when the details are copied into a new record (thus
ruling out Dirty or BeforeUpdate??). I tried using
sfmStakeholder_Details_AfterUpdate but the form loads with the current
date on the active record.

DateChanged is bound to a field with default value of Now( ).

What I have at the moment (where the datechanged does not change at
all) is:
Private Sub DateChanged_BeforeUpdate(Cancel As Integer)
If Format(Me.DateChanged, "dd/mm/yyyy") <> Format(Now(), "dd/Mm/
yyyy") Then
Call Changed
End If
End Sub

Private Sub Changed() 'in the General Declarations of
sfmStakeholder_Details
Me.fUserID = [Forms]![frmLogIn]![fUserID]
Me.DateChanged = Now()
End Sub


Hope I have explained this well enough.
Many thanks for your time and expertise.

Peta

 

[Allen Browne]

Merely viewing a record does not make it dirty.

When you select a record in a bound form, its Current event fires. The
record is not dirtied by doing this.

To track when a record was last changed, use the BeforeUpdate event
procedure of the *form* (not the control), like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.DateChanged = Now()
End Sub

Access does not call the form's BeforeUpdate event unless the record is
dirtied. That's when it's being changed, so that's when you want to record
the change.

If you do this, and you still discover that the DateChanged field is being
changed whenever the record is viewed, it means you have some code in some
event that is dirtying the form whenever you view it. The solution is then
to track down what this is, and eliminate the unnecessary change.

To help you track it down, set the form's RecordSelector property to Yes.
This shows a block on the left the height of the record. A triangle on its
edge indicates which is the current record. That icon changes to a pencil
when the record is dirtied, so you can easily see if you have a dirty record
or not.

To track down what's dirtying the record, look for anything that alters the
contents of a bound field. Code in Form_Current could do it. So could code
in Form_AfterUpdate. The Enter or GotFocus events of controls could also be
the problem.

(All this applies to subform just the same as main forms.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want a textbox (DateChanged) on the subform (sfmStakeholder_Details)
to reflect the date the record was last changed. This subform has a
number of tabs and often I need to view the tabs and/or copy text (Ctrl
+C) without editing or changing DateChanged. There may also be a
number of records (Stakeholder_Details) for each frmStakeholder and I
don't want the date changing when they are passed using the navigation
record selector or when the details are copied into a new record (thus
ruling out Dirty or BeforeUpdate??). I tried using
sfmStakeholder_Details_AfterUpdate but the form loads with the current
date on the active record.

DateChanged is bound to a field with default value of Now( ).

What I have at the moment (where the datechanged does not change at
all) is:
Private Sub DateChanged_BeforeUpdate(Cancel As Integer)
If Format(Me.DateChanged, "dd/mm/yyyy") <> Format(Now(), "dd/Mm/
yyyy") Then
Call Changed
End If
End Sub

Private Sub Changed() 'in the General Declarations of
sfmStakeholder_Details
Me.fUserID = [Forms]![frmLogIn]![fUserID]
Me.DateChanged = Now()
End Sub


Hope I have explained this well enough.
Many thanks for your time and expertise.

Peta

 

[pgcn]

Merely viewing a record does not make it dirty.

When you select a record in a bound form, its Current event fires. The
record is not dirtied by doing this.

To track when a record was last changed, use the BeforeUpdate event
procedure of the *form* (not the control), like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.DateChanged = Now()
End Sub

Access does not call the form's BeforeUpdate event unless the record is
dirtied. That's when it's being changed, so that's when you want to record
the change.

If you do this, and you still discover that the DateChanged field is being
changed whenever the record is viewed, it means you have some code in some
event that is dirtying the form whenever you view it. The solution is then
to track down what this is, and eliminate the unnecessary change.

To help you track it down, set the form's RecordSelector property to Yes.
This shows a block on the left the height of the record. A triangle on its
edge indicates which is the current record. That icon changes to a pencil
when the record is dirtied, so you can easily see if you have a dirty record
or not.

To track down what's dirtying the record, look for anything that alters the
contents of a bound field. Code in Form_Current could do it. So could code
in Form_AfterUpdate. The Enter or GotFocus events of controls could also be
the problem.

(All this applies to subform just the same as main forms.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want a textbox (DateChanged) on the subform (sfmStakeholder_Details)
to reflect thedatethe record was last changed. This subform has a
number of tabs and often I need to view the tabs and/or copy text (Ctrl
+C) without editing or changing DateChanged. There may also be a
number of records (Stakeholder_Details) for each frmStakeholder and I
don't want thedatechanging when they are passed using the navigation
record selector or when the details are copied into a new record (thus
ruling out Dirty or BeforeUpdate??). I tried using
sfmStakeholder_Details_AfterUpdate but the form loads with the current
dateon the active record.

DateChanged is bound to a field with default value of Now( ).

What I have at the moment (where the datechanged does not change at
all) is:
Private Sub DateChanged_BeforeUpdate(Cancel As Integer)
If Format(Me.DateChanged, "dd/mm/yyyy") <> Format(Now(), "dd/Mm/
yyyy") Then
Call Changed
End If
End Sub

Private Sub Changed() 'in the General Declarations of
sfmStakeholder_Details
Me.fUserID = [Forms]![frmLogIn]![fUserID]
Me.DateChanged = Now()
End Sub

Hope I have explained this well enough.
Many thanks for your time and expertise.

Peta- Hide quoted text -

- Show quoted text -

Once again thanks for the reply Allen. I have placed your code in the
BeforeUpdate of the sfm & I do indeed have a dirty form when it is
loaded however I can't identify what is causing it. Below are the
events for the form and subform, if you could have a look for me it
would be appreciated.

The main form has an On Open event which determines the security level
of the user and then sets the visible property of Edit, Delete, Add,
Save & Find buttons.
And an On Load event:
If Len(Me.OpenArgs) > 0 Then
If Me.OpenArgs = "Find" Then
Me.frmSHDetails.Enabled = True
Me.frmSHDetails.Visible = True
Exit Sub
End If
Me.frmSHDetails.Enabled = False
Me.frmSHDetails.Visible = False
Me.SHLName = Me.OpenArgs
DoCmd.GoToControl "SHLNAME"
Else
Me.RecordSource = "SELECT ...
End If
End Sub

I'm guessing neither of these events would trigger a BeforeUpdate??.

The sfmStakeholder_Details has an On Current which sets the recordset
and counts the number of active and inactive projects for the
Stakeholder_DetailsID in another table.
If rst.RecordCount >= 0 Then
If rst!countoffProjectId = -(rst!SumOfInactive) Then
Me.PInactive.Visible = True 'Persona Inactive
bitmap
Me.Inactive = True 'a check box
Else
Me.PInactive.Visible = False
Me.Inactive = False
End If
Else
Me.PInactive.Visible = False
End If

The DateChanged is updating on the first record whether or not it is
inactive so I don't think that is the problem.

No contents of bound fields are changing except for the display of the
SH_DetailsID when you view a new Detail record. On these subsequent
records the DateChanged updates as soon as Edit is clicked (not
preferred as sometimes no changes are made). This Edit On Click has:

EditDoc Me.Name
EditSubforms
& makes the Add, Delete, & Save buttons visible on the main form, sets
the focus to FName and hides the Edit button.

Only one of the text controls has an AfterUpdate which simply copies
its contents to another control.
I'm not sure of where to go from here.
Cheers

 

[Rick Brandt]

The sfmStakeholder_Details has an On Current which sets the recordset
and counts the number of active and inactive projects for the
Stakeholder_DetailsID in another table.
If rst.RecordCount >= 0 Then
If rst!countoffProjectId = -(rst!SumOfInactive) Then
Me.PInactive.Visible = True 'Persona Inactive
bitmap
Me.Inactive = True 'a check box
Else
Me.PInactive.Visible = False
Me.Inactive = False
End If
Else
Me.PInactive.Visible = False
End If

The DateChanged is updating on the first record whether or not it is
inactive so I don't think that is the problem.

I'm willing to bet it almost certainly IS the problem. The only time you
are not setting a value is if rst.RecordCount is zero.

Just comment out that block of code and then try your form. You should know
in a few seconds if that is where the problem is.

 

[pgcn]

I'm willing to bet it almost certainly IS the problem.  The only time you
are not setting a value is if rst.RecordCount is zero.

Just comment out that block of code and then try your form.  You should know
in a few seconds if that is where the problem is.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com- Hide quoted text -

- Show quoted text -

Thanks Rick - yes commenting out the If rst.RecordCount >= 0 Then ...
stopped the updating of the DateChanged on load, but of course also
the display of the bitmap if inactive. Any suggestion of how to keep
this feature?

thanks

 

[Rick Brandt]

Thanks Rick - yes commenting out the If rst.RecordCount >= 0 Then ...
stopped the updating of the DateChanged on load, but of course also
the display of the bitmap if inactive. Any suggestion of how to keep
this feature?

You can toggle the visibility of the bitmap. It is the setting of the
CheckBox that is dirtying the record.

 

[pgcn]

You can toggle the visibility of the bitmap.  It is the setting of the
CheckBox that is dirtying the record.

I can't get rid of the CheckBox as queries rely on it. Does this mean
I can't use the sfm AfterUpdate to record the edit date? I don't know
how to toggle the visibility of the bitmap.

Further instructions/work around would be great.
thanks a lot