Time Stamp Issue

[Antavas]

I have it set up in my table that everytime a record is created it is time
stamped with the date.

What I am trying to do is set up a separate time stamp when the record is
modified while also keeping the original record creation time stamp.

Is this possible and if so how can I do it?

 

[Arvin Meyer [MVP]]

Unless you add another table you cannot monitor every change, but you can
add a field to the current table to see when the record is last modified.
Use the BeforeUpdate event of your form to write the current timestamp (and
username if you'd like) to the underlying record. When a record is
unmodified, the created date/time and the last modified date/time will be
the same. After modification, they're different. For the Windows UserName
have a look at:

http://www.mvps.org/access/api/api0008.htm

 

[Jerry Whittle]

In Access you can only do this if modifying the data when using a form. Put a
date/time field in the table called something like ModifiedDate. Make sure
that this field is seen by the form's record source . If you are using a
query to populate the form, you will need to add it to the query.

Add the ModifiedDate field to the form. You can make it so small so that you
can't even see it if you wish.

Then on the Before Update event of the form, do something like below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True Then
Me.ModifiedDate = Now()
End If
End Sub

That should do it. It will keep the last time that the record was modified
when using a form.

 

[John Spencer]

Yes it is possible as long as you do all data entry using a form.

In the form's before update event add code to set the LastModified field in
the table.

Me!LastModified = Date()
Or if you want the date and time use Now() in place of Date().

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Aria]

I'm sorry to just jump into your post Antavas but I have a similar question
and hope you don't mind. How would you do this if you want to also track
changes to a subform? I have a form with a subform. The form has a date
modified field. I would like to track any changes that occur, whether they
are on the form or subform.
This is what I am currently using in the Before Update event:
Me!DateModified = Date

Thank you.

 

[Arvin Meyer [MVP]]

The subform has multiple records, so you should write to the table/query
which is the recordsource of that form. Use the same code in the subform,
just add the field to that underlying table.

 

[Arvin Meyer [MVP]]

No, don't add the tblPhones to the subform recordsource.

You need to add a DateEdited field to the junction table, that's the many
side of table phones.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I had trouble posting to the MS site. Gee Arvin, it sounds so simple when
you
say it. When I try to do that I end up with problems. My form is called
frmPhones with a subform titled sfrmPhoneAssociations.

This is the SQL for frmPhones:
SELECT tblPhones.PhoneID, tblPhones.PhoneDescription,
tblPhones.PhoneNumber,
tblPhones.PhoneType, tblPhones.EmpID, tblPhones.BuildingID, tblPhones.
DateCreated, tblPhones.DateEdited
FROM tblPhones
ORDER BY tblPhones.PhoneNumber;

This is the recordsource of the subform:

SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID, tblEmployees.FirstName,
tblEmployees.LastName
FROM tblEmployees INNER JOIN tblPhonesEmps ON tblEmployees.EmpID =
tblPhonesEmps.EmpID
ORDER BY tblEmployees.LastName;

tblPhonesEmps is the juction table. If I understand you correctly, I need
to
add DateEdited (located in tblPhones) to the recordsource of
sfrmPhoneAssociations, correct? When I try to add tblPhones (the table
that
has the DateEdited field), I no longer see the records that are returned
in
datasheet view. It's now blank.

The subform has multiple records, so you should write to the table/query
which is the recordsource of that form. Use the same code in the subform,
just add the field to that underlying table.

I'm sorry to just jump into your post Antavas but I have a similar
question

[quoted text clipped - 39 lines]

Is this possible and if so how can I do it?

 

[Aria]

Maybe I’m leaving out critical information…I added DateEdited as a field in
the junction table (tblPhoneEmps) and also to the recordsource:

SELECT [tblPhonesEmps].[PhoneID], [tblPhonesEmps].[EmpID],
[tblEmployees].[FirstName], [tblEmployees].[LastName],
[tblPhonesEmps].[DateEdited] FROM tblEmployees INNER JOIN tblPhonesEmps ON
[tblEmployees].[EmpID]=[tblPhonesEmps].[EmpID] ORDER BY
[tblEmployees].[LastName];

and to the BeforeUpdate event of the subform (sfrmPhoneAssociations):

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleError
Me!DateEdited = Date
exit_procedure:
Exit Sub
HandleError:
ErrorHandler Err.Number, Err.Description, "sfrmPhoneAssociations",
"Form_BeforeUpdate"
Resume exit_procedure

End Sub

I tried testing by changing a few records in the subform. I don’t see a
change in txtDateEdited so there must be some information that I failed to
provide.

Trying again:
1.frmPhones is a tabbed form with a subform (sfrmPhoneAssociations).

2.The subform (sfrmPhoneAssociations) is a continuous form consisting of a
combo box (cboEmpID) and a delete command button.

3.The tab control has since as part of the On Change event:

Private Sub TabCtl0_Change()
On Error GoTo HandleError

Select Case Me.TabCtl0 'the Tab Control's current value

Case 1 'the second Page is selected, frmPhones

Me.frmPhones.Form!sfrmPhoneAssociations.Form!cboEmpID.Requery

My thought was that if there was a change to the subform such as deleting or
adding and employee, that would be reflected in the date edited field. I know
I’m making this harder than it should be but what can I say, I’m still a
beginner. <g>

--
Aria W.

No, don't add the tblPhones to the subform recordsource.

You need to add a DateEdited field to the junction table, that's the many
side of table phones.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I had trouble posting to the MS site. Gee Arvin, it sounds so simple when
you
say it. When I try to do that I end up with problems. My form is called
frmPhones with a subform titled sfrmPhoneAssociations.

This is the SQL for frmPhones:
SELECT tblPhones.PhoneID, tblPhones.PhoneDescription,
tblPhones.PhoneNumber,
tblPhones.PhoneType, tblPhones.EmpID, tblPhones.BuildingID, tblPhones.
DateCreated, tblPhones.DateEdited
FROM tblPhones
ORDER BY tblPhones.PhoneNumber;

This is the recordsource of the subform:

SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID, tblEmployees.FirstName,
tblEmployees.LastName
FROM tblEmployees INNER JOIN tblPhonesEmps ON tblEmployees.EmpID =
tblPhonesEmps.EmpID
ORDER BY tblEmployees.LastName;

tblPhonesEmps is the juction table. If I understand you correctly, I need
to
add DateEdited (located in tblPhones) to the recordsource of
sfrmPhoneAssociations, correct? When I try to add tblPhones (the table
that
has the DateEdited field), I no longer see the records that are returned
in
datasheet view. It's now blank.

The subform has multiple records, so you should write to the table/query
which is the recordsource of that form. Use the same code in the subform,
just add the field to that underlying table.
I'm sorry to just jump into your post Antavas but I have a similar
question
[quoted text clipped - 39 lines]

Is this possible and if so how can I do it?

.

 

[tomas]

I had trouble posting to the MS site. Gee Arvin, it sounds so simple when
you
say it. When I try to do that I end up with problems. My form is called
frmPhones with a subform titled sfrmPhoneAssociations.

This is the SQL for frmPhones:
SELECT tblPhones.PhoneID, tblPhones.PhoneDescription,
tblPhones.PhoneNumber,
tblPhones.PhoneType, tblPhones.EmpID, tblPhones.BuildingID, tblPhones.
DateCreated, tblPhones.DateEdited
FROM tblPhones
ORDER BY tblPhones.PhoneNumber;

This is the recordsource of the subform:

SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID, tblEmployees.FirstName,
tblEmployees.LastName
FROM tblEmployees INNER JOIN tblPhonesEmps ON tblEmployees.EmpID =
tblPhonesEmps.EmpID
ORDER BY tblEmployees.LastName;

tblPhonesEmps is the juction table. If I understand you correctly, I need
to
add DateEdited (located in tblPhones) to the recordsource of
sfrmPhoneAssociations, correct? When I try to add tblPhones (the table
that
has the DateEdited field), I no longer see the records that are returned
in
datasheet view. It's now blank.

The subform has multiple records, so you should write to the table/query
which is the recordsource of that form. Use the same code in the subform,
just add the field to that underlying table.

I'm sorry to just jump into your post Antavas but I have a similar
question

[quoted text clipped - 39 lines]

Is this possible and if so how can I do it?

 

[Arvin Meyer [MVP]]

Events in controls only fire when you physically do something to the
control, so setting focus, requerying, or anything else that might cause a
change through code will not fire an event. Secondly, merely adding a field
to an underlying recordset won't allow data to be entered unless you use an
insert query. Add a textbox to the form (it can be hidden) bound to the
DateEdited field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Maybe I'm leaving out critical information.I added DateEdited as a field
in
the junction table (tblPhoneEmps) and also to the recordsource:

SELECT [tblPhonesEmps].[PhoneID], [tblPhonesEmps].[EmpID],
[tblEmployees].[FirstName], [tblEmployees].[LastName],
[tblPhonesEmps].[DateEdited] FROM tblEmployees INNER JOIN tblPhonesEmps ON
[tblEmployees].[EmpID]=[tblPhonesEmps].[EmpID] ORDER BY
[tblEmployees].[LastName];

and to the BeforeUpdate event of the subform (sfrmPhoneAssociations):

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleError
Me!DateEdited = Date
exit_procedure:
Exit Sub
HandleError:
ErrorHandler Err.Number, Err.Description, "sfrmPhoneAssociations",
"Form_BeforeUpdate"
Resume exit_procedure

End Sub

I tried testing by changing a few records in the subform. I don't see a
change in txtDateEdited so there must be some information that I failed to
provide.

Trying again:
1.frmPhones is a tabbed form with a subform (sfrmPhoneAssociations).

2.The subform (sfrmPhoneAssociations) is a continuous form consisting of a
combo box (cboEmpID) and a delete command button.

3.The tab control has since as part of the On Change event:

Private Sub TabCtl0_Change()
On Error GoTo HandleError

Select Case Me.TabCtl0 'the Tab Control's current value

Case 1 'the second Page is selected, frmPhones

Me.frmPhones.Form!sfrmPhoneAssociations.Form!cboEmpID.Requery

My thought was that if there was a change to the subform such as deleting
or
adding and employee, that would be reflected in the date edited field. I
know
I'm making this harder than it should be but what can I say, I'm still a
beginner. <g>

--
Aria W.

No, don't add the tblPhones to the subform recordsource.

You need to add a DateEdited field to the junction table, that's the many
side of table phones.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I had trouble posting to the MS site. Gee Arvin, it sounds so simple
when
you
say it. When I try to do that I end up with problems. My form is called
frmPhones with a subform titled sfrmPhoneAssociations.

This is the SQL for frmPhones:
SELECT tblPhones.PhoneID, tblPhones.PhoneDescription,
tblPhones.PhoneNumber,
tblPhones.PhoneType, tblPhones.EmpID, tblPhones.BuildingID, tblPhones.
DateCreated, tblPhones.DateEdited
FROM tblPhones
ORDER BY tblPhones.PhoneNumber;

This is the recordsource of the subform:

SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID,
tblEmployees.FirstName,
tblEmployees.LastName
FROM tblEmployees INNER JOIN tblPhonesEmps ON tblEmployees.EmpID =
tblPhonesEmps.EmpID
ORDER BY tblEmployees.LastName;

tblPhonesEmps is the juction table. If I understand you correctly, I
need
to
add DateEdited (located in tblPhones) to the recordsource of
sfrmPhoneAssociations, correct? When I try to add tblPhones (the table
that
has the DateEdited field), I no longer see the records that are
returned
in
datasheet view. It's now blank.


Arvin Meyer [MVP] wrote:
The subform has multiple records, so you should write to the
table/query
which is the recordsource of that form. Use the same code in the
subform,
just add the field to that underlying table.
I'm sorry to just jump into your post Antavas but I have a similar
question
[quoted text clipped - 39 lines]

Is this possible and if so how can I do it?

.

 

[Aria]

OK. I thought it must be something like that since I didn't see a change.
Thank you very much for the information and your help with this Arvin.
--
Aria W.

Events in controls only fire when you physically do something to the
control, so setting focus, requerying, or anything else that might cause a
change through code will not fire an event. Secondly, merely adding a field
to an underlying recordset won't allow data to be entered unless you use an
insert query. Add a textbox to the form (it can be hidden) bound to the
DateEdited field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Maybe I'm leaving out critical information.I added DateEdited as a field
in
the junction table (tblPhoneEmps) and also to the recordsource:

SELECT [tblPhonesEmps].[PhoneID], [tblPhonesEmps].[EmpID],
[tblEmployees].[FirstName], [tblEmployees].[LastName],
[tblPhonesEmps].[DateEdited] FROM tblEmployees INNER JOIN tblPhonesEmps ON
[tblEmployees].[EmpID]=[tblPhonesEmps].[EmpID] ORDER BY
[tblEmployees].[LastName];

and to the BeforeUpdate event of the subform (sfrmPhoneAssociations):

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleError
Me!DateEdited = Date
exit_procedure:
Exit Sub
HandleError:
ErrorHandler Err.Number, Err.Description, "sfrmPhoneAssociations",
"Form_BeforeUpdate"
Resume exit_procedure

End Sub

I tried testing by changing a few records in the subform. I don't see a
change in txtDateEdited so there must be some information that I failed to
provide.

Trying again:
1.frmPhones is a tabbed form with a subform (sfrmPhoneAssociations).

2.The subform (sfrmPhoneAssociations) is a continuous form consisting of a
combo box (cboEmpID) and a delete command button.

3.The tab control has since as part of the On Change event:

Private Sub TabCtl0_Change()
On Error GoTo HandleError

Select Case Me.TabCtl0 'the Tab Control's current value

Case 1 'the second Page is selected, frmPhones

Me.frmPhones.Form!sfrmPhoneAssociations.Form!cboEmpID.Requery

My thought was that if there was a change to the subform such as deleting
or
adding and employee, that would be reflected in the date edited field. I
know
I'm making this harder than it should be but what can I say, I'm still a
beginner. <g>

--
Aria W.

No, don't add the tblPhones to the subform recordsource.

You need to add a DateEdited field to the junction table, that's the many
side of table phones.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I had trouble posting to the MS site. Gee Arvin, it sounds so simple
when
you
say it. When I try to do that I end up with problems. My form is called
frmPhones with a subform titled sfrmPhoneAssociations.

This is the SQL for frmPhones:
SELECT tblPhones.PhoneID, tblPhones.PhoneDescription,
tblPhones.PhoneNumber,
tblPhones.PhoneType, tblPhones.EmpID, tblPhones.BuildingID, tblPhones.
DateCreated, tblPhones.DateEdited
FROM tblPhones
ORDER BY tblPhones.PhoneNumber;

This is the recordsource of the subform:

SELECT tblPhonesEmps.PhoneID, tblPhonesEmps.EmpID,
tblEmployees.FirstName,
tblEmployees.LastName
FROM tblEmployees INNER JOIN tblPhonesEmps ON tblEmployees.EmpID =
tblPhonesEmps.EmpID
ORDER BY tblEmployees.LastName;

tblPhonesEmps is the juction table. If I understand you correctly, I
need
to
add DateEdited (located in tblPhones) to the recordsource of
sfrmPhoneAssociations, correct? When I try to add tblPhones (the table
that
has the DateEdited field), I no longer see the records that are
returned
in
datasheet view. It's now blank.


Arvin Meyer [MVP] wrote:
The subform has multiple records, so you should write to the
table/query
which is the recordsource of that form. Use the same code in the
subform,
just add the field to that underlying table.
I'm sorry to just jump into your post Antavas but I have a similar
question
[quoted text clipped - 39 lines]

Is this possible and if so how can I do it?

--




.

.