Updating Date In a Form..

[Goobz]

I was wondering if anyone can tell me if there's a way to have the
time populate a locked field in a form, and have it store to the table
that I am working on.. I am trying to record when records are
accessed, and save it to the table entry.

 

[Jeff Boyce]

One way to do this is to add a field in the table to hold the date/time of
the last time the record was updated, then add code to the event procedure
that your form will run when you save the change. That code sets the value
of the [Updated] control to Now().

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Goobz]

One way to do this is to add a field in the table to hold the date/time of
the last time the record was updated, then add code to the event procedure
that your form will run when you save the change. That code sets the value
of the [Updated] control to Now().

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was wondering if anyone can tell me if there's a way to have the
time populate a locked field in a form, and have it store to the table
that I am working on.. I am trying to record when records are
accessed, and save it to the table entry.- Hide quoted text -

- Show quoted text -

I already added the field "Last Record Update" to the "DN" Table that
I am working on. The only thing I can't figure out, is how to create
this event procedure. Would it be too much to ask of you for
assistance!?

 

[Pat Hartman]

Updated is easy, you would put the code:

Me.UpdatedDate = Now()
in the Form's BeforeUpdate event.

If you really mean that you want to update this field when anyone just looks
at a record, you would put the code into the Form's Current event.

 

[Goobz]

Updated is easy, you would put the code:

Me.UpdatedDate = Now()
in the Form's BeforeUpdate event.

If you really mean that you want to update this field when anyone just looks
at a record, you would put the code into the Form's Current event.






- Show quoted text -

I must just be doing SOMETHING wrong.. Here's what I have done, and it
just is not working...

Name.... LastAccessed
Control Source.... Last Accessed
Enabled.... Yes
Locked.... Yes
Before Update....[Event Procedure]
After Update.... Nothing
On (All On items Are the same answer).... Nothing

As for the [Event Procedure] Code...

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command31_Click:
Exit Sub
Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub
Private Sub LastAccessed_BeforeUpdate(Cancel As Integer)
Me.UpdatedDate = Now()
End Sub

As for the answer of how I need it to update... I need the field to
update ONLY when someone makes a change to one of the fields in the
form. That way, when I build a Query against the date, I can see when
the last change was.

Thanx so far!

 

[Pat Hartman]

What is "LastAccessed"? Are you only trying to track updates to one field?
Usually the Last update would refer to ANY record change, not just a change
to a single field. That's why I suggested the form's BeforeUpdate event
rather than the BeforeUpdate event of each control.

Now for some professional advice. When you add controls to a form/report
manually, go in to the properties and change the Name property before you
add any code. In two months are you going to know what Command31 is? If
you change the name now, Access will loose its association with the existing
code so you'll need to move it from where it is to the click event with the
new name. Then delete the code for Command31. Secondly, and this is
Microsoft's fault not yours, the wizards usually generate DoMenuItem
commands that are based on the menu structure of Access95! Do you know what
the 10th item in the Edit Menu of Access 95 is? I don't either. so I have
no idea what the click event is actually going to do. So now, while the
purpose of this code is still clear to you, change it to use the appropriate
DoCmd.RunCommand method. Intellisense will give you a list of available
values. You just need to scroll to the one you want. Keep in mind that for
record operations, the command will end with "Record". For example
DoCmd.RunCommand acCmdSave will save the form NOT the record.
DoCmd.RunCommand acCmdSaveRecord will save the current record.

Updated is easy, you would put the code:

Me.UpdatedDate = Now()
in the Form's BeforeUpdate event.

If you really mean that you want to update this field when anyone just
looks
at a record, you would put the code into the Form's Current event.






- Show quoted text -

I must just be doing SOMETHING wrong.. Here's what I have done, and it
just is not working...

Name.... LastAccessed
Control Source.... Last Accessed
Enabled.... Yes
Locked.... Yes
Before Update....[Event Procedure]
After Update.... Nothing
On (All On items Are the same answer).... Nothing

As for the [Event Procedure] Code...

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command31_Click:
Exit Sub
Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub
Private Sub LastAccessed_BeforeUpdate(Cancel As Integer)
Me.UpdatedDate = Now()
End Sub

As for the answer of how I need it to update... I need the field to
update ONLY when someone makes a change to one of the fields in the
form. That way, when I build a Query against the date, I can see when
the last change was.

Thanx so far!

 

[Goobz]

What is "LastAccessed"? Are you only trying to track updates to one field?
Usually the Last update would refer to ANY record change, not just a change
to a single field. That's why I suggested the form's BeforeUpdate event
rather than the BeforeUpdate event of each control.

Now for some professional advice. When you add controls to a form/report
manually, go in to the properties and change the Name property before you
add any code. In two months are you going to know what Command31 is? If
you change the name now, Access will loose its association with the existing
code so you'll need to move it from where it is to the click event with the
new name. Then delete the code for Command31. Secondly, and this is
Microsoft's fault not yours, the wizards usually generate DoMenuItem
commands that are based on the menu structure of Access95! Do you know what
the 10th item in the Edit Menu of Access 95 is? I don't either. so I have
no idea what the click event is actually going to do. So now, while the
purpose of this code is still clear to you, change it to use the appropriate
DoCmd.RunCommand method. Intellisense will give you a list of available
values. You just need to scroll to the one you want. Keep in mind that for
record operations, the command will end with "Record". For example
DoCmd.RunCommand acCmdSave will save the form NOT the record.
DoCmd.RunCommand acCmdSaveRecord will save the current record.

I must just be doing SOMETHING wrong.. Here's what I have done, and it
just is not working...

Name.... LastAccessed
Control Source.... Last Accessed
Enabled.... Yes
Locked.... Yes
Before Update....[Event Procedure]
After Update.... Nothing
On (All On items Are the same answer).... Nothing

As for the [Event Procedure] Code...

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command31_Click:
Exit Sub
Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub
Private Sub LastAccessed_BeforeUpdate(Cancel As Integer)
Me.UpdatedDate = Now()
End Sub

As for the answer of how I need it to update... I need the field to
update ONLY when someone makes a change to one of the fields in the
form. That way, when I build a Query against the date, I can see when
the last change was.

Thanx so far!- Hide quoted text -

- Show quoted text -

Pat...

Thank you for the VERY informative heads up... As for my trackings, I
am wanting to track ***ANY*** changes to the forms 13 fields. I **DO
NOT** want to track simply click and views, if that makes sense...

I am in the process of cleaning up a 5 year old database that has been
imported and cleaned from every version of Access all the way from
`95. The person that added fields and stuff never named them... I
cleaned up the Events page, and here is the latest... Please let me
know what I need to change to include the record save.

-------------------------------
Option Compare Database

Private Sub Search_Button_Click()
On Error GoTo Err_Search_Button_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Button_Click:
Exit Sub

Err_Search_Button_Click:
MsgBox Err.Description
Resume Exit_Search_Button_Click
End Sub

Private Sub LastAccessed_BeforeUpdate(Cancel As Integer)
Me.UpdatedDate = Now()
DoCmd.RunCommand acCmdSaveRecord
-------------------------------------------

And a bit of how this form is layed out.....

Field 1 - Extension # - Not Locked/Active
Field 2 - Name - Locked/Active
Field 3 - Location - Locked/Active
Field 4 - Date of Last Change - Locked/Active

Those are fields that whenever someone pulls up the database, they can
see those fields, and the only one they can change is Extension #.

Again, thanx for all your help...

Charles