Update Query and Timestamp

[TotallyConfused]

I need to update a main table with data from other tables. However, I need
to keep track of the changes to the main table. I thought of adding a field
to the main table with a datestamp. When the records get updated a datestamp
would be automatically entered. How do I set this up? Is there an easier or
better way of doing this? Thank you in advance for any help provided.

 

[Michel Walsh]

If the modifications occur through a form, in the form before update event
procedure, update the said field:


Me.TimeStampFieldNameHere = Now()



(That assumes your form is bound to your table. In fact,
Me.TimeStampFieldNameHere is an expression which refers to the control on
the form, not to the table directly, but indirectly, that does the job...)

Hoping it may help,
Vanderghast, Access MVP

 

[TotallyConfused]

Thank you for responding. I am working on a query not through a form. This
is what I have. However, I am not able to update the fields, "Contact" or
"Sent". But I do get the timestamp. Can you please tell me what I am doing
wrong? thank you. The tables are identical just need to update Main table
with Tracking Copy 1.

UPDATE [TRACKING COPY 1] INNER JOIN [MAIN TRCKG ] ON [TRACKING COPY 1].ID =
[MAIN TRCKG].ID SET [TRACKING COPY 1].[Contact] = [MAIN TRCKG].[CONTACT],
[MAIN TRCKG].Updatedate = Now(), [TRACKING COPY 1].[Sent] = [MAIN
TRCKG].[Sent]
WHERE ((([TRACKING COPY 1].[Company])="VANDERBILT"));

 

[Michel Walsh]

If you update it through the user interface, do you get the message that
some records could not be updated because of no respect of referential data
integrity? It may be that [TRACKING COPY 1].Contact *must* be present in
another table and the new proposed Contact value just won't make that
happen.


Vanderghast, Access MVP

Thank you for responding. I am working on a query not through a form.
This
is what I have. However, I am not able to update the fields, "Contact" or
"Sent". But I do get the timestamp. Can you please tell me what I am
doing
wrong? thank you. The tables are identical just need to update Main
table
with Tracking Copy 1.

UPDATE [TRACKING COPY 1] INNER JOIN [MAIN TRCKG ] ON [TRACKING COPY 1].ID
=
[MAIN TRCKG].ID SET [TRACKING COPY 1].[Contact] = [MAIN TRCKG].[CONTACT],
[MAIN TRCKG].Updatedate = Now(), [TRACKING COPY 1].[Sent] = [MAIN
TRCKG].[Sent]
WHERE ((([TRACKING COPY 1].[Company])="VANDERBILT"));

If the modifications occur through a form, in the form before update
event
procedure, update the said field:


Me.TimeStampFieldNameHere = Now()



(That assumes your form is bound to your table. In fact,
Me.TimeStampFieldNameHere is an expression which refers to the control on
the form, not to the table directly, but indirectly, that does the
job...)

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Furthermore, from the user interface (query designer), if you change the
UPDATE query to a SELECT query (from the user interface), are the fields
from [Main Trckg] with the right (and acceptable, ie, not null) values ?

 

[TotallyConfused]

Yes, the only field that is getting updated in the main table is update field
with the current date. Why are my other fields not updating? Contact field
is a text field and Sent is a date field. What is wrong witht the SQL?

 

[Michel Walsh]

It seems there is nothing wrong in your SQL statement itself, but the inter
tables relations may not allow your fields to get the assigned values. As
example, if a field is obliged to get one of the values "Jan", "Feb", "Mar"
(from a relationship), then, you won't be able to assign it the value "Apr"
and if you try, the record won't be updated.

Another possibility is that the record is updated multiple time (which is
possible if the join find multiple matches) and so, will keep only the last
modification. A little bit like:

Public Sub Foo()
Dim A as Integer

A=1
' and later
A=2
' and later
A=1
End Sub



And it happens that the last update done in your field is the initial value!
So, the update seems to NOT work, but it does... not the way you want
though.


That is why using a SELECT rather than an UPDATE could help you to diagnose
the problem: Is the record to be updated appears only once? are the values
updating the field, from the other table, are the right values you intended?


Vanderghast, Access MVP

Yes, the only field that is getting updated in the main table is update
field
with the current date. Why are my other fields not updating? Contact
field
is a text field and Sent is a date field. What is wrong witht the SQL?

Furthermore, from the user interface (query designer), if you change the
UPDATE query to a SELECT query (from the user interface), are the fields
from [Main Trckg] with the right (and acceptable, ie, not null) values ?

 

[TotallyConfused]

Okay, how can I fix? I have switched to a SELECT and the fields and data
show up. Values are right. What else can I do to update my main table from
the copy table?

It seems there is nothing wrong in your SQL statement itself, but the inter
tables relations may not allow your fields to get the assigned values. As
example, if a field is obliged to get one of the values "Jan", "Feb", "Mar"
(from a relationship), then, you won't be able to assign it the value "Apr"
and if you try, the record won't be updated.

Another possibility is that the record is updated multiple time (which is
possible if the join find multiple matches) and so, will keep only the last
modification. A little bit like:

Public Sub Foo()
Dim A as Integer

A=1
' and later
A=2
' and later
A=1
End Sub



And it happens that the last update done in your field is the initial value!
So, the update seems to NOT work, but it does... not the way you want
though.


That is why using a SELECT rather than an UPDATE could help you to diagnose
the problem: Is the record to be updated appears only once? are the values
updating the field, from the other table, are the right values you intended?


Vanderghast, Access MVP

Yes, the only field that is getting updated in the main table is update
field
with the current date. Why are my other fields not updating? Contact
field
is a text field and Sent is a date field. What is wrong witht the SQL?

Furthermore, from the user interface (query designer), if you change the
UPDATE query to a SELECT query (from the user interface), are the fields
from [Main Trckg] with the right (and acceptable, ie, not null) values ?

 

[Michel Walsh]

To see if there is a problem of relation between another table, try the
following:

Open the table to be updated (directly, in table view),
Move to one of the record to be updated,
change the values, in the direct table view, in the two fields with values
you see in the update query now in a select view
try to save the record (changing row) in the direct table view.

If an error occur, by an update in the direct table view, definitively, the
update query is not the problem.

If no error occur, there is still the possibility that the same row appears
many time, in the update query. To check that, in the update query for now a
Select query, data view, sort the view over the field Contact. It would be
easier to see if the same contact is then updated many times (because it
appears many time). If so, does the updating values are ALL right (if not,
that may be a problem).



Vanderghast, Access MVP

Okay, how can I fix? I have switched to a SELECT and the fields and data
show up. Values are right. What else can I do to update my main table
from
the copy table?

It seems there is nothing wrong in your SQL statement itself, but the
inter
tables relations may not allow your fields to get the assigned values. As
example, if a field is obliged to get one of the values "Jan", "Feb",
"Mar"
(from a relationship), then, you won't be able to assign it the value
"Apr"
and if you try, the record won't be updated.

Another possibility is that the record is updated multiple time (which is
possible if the join find multiple matches) and so, will keep only the
last
modification. A little bit like:

Public Sub Foo()
Dim A as Integer

A=1
' and later
A=2
' and later
A=1
End Sub



And it happens that the last update done in your field is the initial
value!
So, the update seems to NOT work, but it does... not the way you want
though.


That is why using a SELECT rather than an UPDATE could help you to
diagnose
the problem: Is the record to be updated appears only once? are the
values
updating the field, from the other table, are the right values you
intended?


Vanderghast, Access MVP

Yes, the only field that is getting updated in the main table is update
field
with the current date. Why are my other fields not updating? Contact
field
is a text field and Sent is a date field. What is wrong witht the SQL?

:

Furthermore, from the user interface (query designer), if you change
the
UPDATE query to a SELECT query (from the user interface), are the
fields
from [Main Trckg] with the right (and acceptable, ie, not null) values
?