adding information/keeping history of user name and time stamp

i have just created a simple append query....

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.ID, FL.Name, FL.[Inv No], FL.Model, FL.Serial, FL.TY, FL.[ORIG
AMT], FL.[BAL DUE], FL.[INV DATE], FL.[Action Needed], FL.Resolution,
FL.Status, FL.Time_Stamp, FL.[Action User], FL.[Resolution User], FL.Memo
FROM FL

THANK YOU!
"Lynn Trapp" wrote:

Could you post the append query that you have?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Hi,
What code do i need to use to have it append only the record the user has
touched? I have created a append query, but this will append all the
records
from the main table to history table.
THank you!

"Lynn Trapp" wrote:

Well, you can add the UserName to your field in the AfterUpdate event of
your form AND immediately append the record to another table with the
same
structure -- a sort of archive table. This will effectively give you a
record of every user who has updated every record in your original table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
yes, it is so difficult designing this database because there are no
unique
field. The system the team uses, you can key same invoice #, date,
Amnt
and
Serial #'s. So when I pull a report and import into database, I can't
set
the primary key(s). What would you suggest? Should I just not have
the
history table added ?

"Lynn Trapp" wrote:

Uh Oh! Not a good deal. One of the most important reasons for having a
relational database is to avoid duplicate data. No, an AutoNumber
field
won't work and, while it would allow you to create the link I
discussed,
it
would not prevent the entry of duplicate invoice numbers and serial
numbers.
It would just hide their existence. If you need unique invoice numbers
and
serial numbers, then you will need to add something, like a unique
index,
on
those fields to prevent that.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
problem i have is that the table don't have primary key because
there
are
no
unique characters. for example: there are duplicate invoices #'s and
serial
#'s. I couldn't make anything as primary key. Adding autonumber
will
now
work because it will not be the same number in the history table,
correct?
Thank you!

"Lynn Trapp" wrote:

Then you will need to add a history table that is linked one to
many
to
your main table. You can then set the value to CurrentUser in the
AfterUpdate event of your data entry form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Store every person who modified the record.

Thank you for your help!

"Lynn Trapp" wrote:

Do you want to simply store the last person who modified the
record
or
do
you want to store every person who modifies the record?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Hello,
I was builiding a database for co-worker of mine.
I have a form with drop down box with usernames, Status and
when
status
changes to "Complete" it automatically time stamps.
She wants to keep the history of who touched that record.
Is there anyway to concatenate username, status and time stamp
to
another
column name "History" and keep adding to that every time that
record
is
touched by someone? For Example:.
Invoice # Invoice Date Username Status Time
STamp
Hisotry
123 2/15/2005 JOE Complete
1:15pm
Joe
Complete 1:15

Smith Complete 2:35


Or can anyone recommend better solution for this? any help
would
appreciate
it!
Thank you!

Try changing that query to something like this:

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.*
FROM FL
WHERE FL.ID = Me.txtID

This assumes that you have a control on your form that has FL.ID as it's
control source and you have named that control txtID.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
i have just created a simple append query....

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG
AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.ID, FL.Name, FL.[Inv No], FL.Model, FL.Serial, FL.TY, FL.[ORIG
AMT], FL.[BAL DUE], FL.[INV DATE], FL.[Action Needed], FL.Resolution,
FL.Status, FL.Time_Stamp, FL.[Action User], FL.[Resolution User], FL.Memo
FROM FL

THANK YOU!
"Lynn Trapp" wrote:

Could you post the append query that you have?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Hi,
What code do i need to use to have it append only the record the user
has
touched? I have created a append query, but this will append all the
records
from the main table to history table.
THank you!

"Lynn Trapp" wrote:

Well, you can add the UserName to your field in the AfterUpdate event
of
your form AND immediately append the record to another table with the
same
structure -- a sort of archive table. This will effectively give you a
record of every user who has updated every record in your original
table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
yes, it is so difficult designing this database because there are no
unique
field. The system the team uses, you can key same invoice #, date,
Amnt
and
Serial #'s. So when I pull a report and import into database, I
can't
set
the primary key(s). What would you suggest? Should I just not have
the
history table added ?

"Lynn Trapp" wrote:

Uh Oh! Not a good deal. One of the most important reasons for
having a
relational database is to avoid duplicate data. No, an AutoNumber
field
won't work and, while it would allow you to create the link I
discussed,
it
would not prevent the entry of duplicate invoice numbers and serial
numbers.
It would just hide their existence. If you need unique invoice
numbers
and
serial numbers, then you will need to add something, like a unique
index,
on
those fields to prevent that.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
problem i have is that the table don't have primary key because
there
are
no
unique characters. for example: there are duplicate invoices #'s
and
serial
#'s. I couldn't make anything as primary key. Adding autonumber
will
now
work because it will not be the same number in the history table,
correct?
Thank you!

"Lynn Trapp" wrote:

Then you will need to add a history table that is linked one to
many
to
your main table. You can then set the value to CurrentUser in
the
AfterUpdate event of your data entry form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Store every person who modified the record.

Thank you for your help!

"Lynn Trapp" wrote:

Do you want to simply store the last person who modified the
record
or
do
you want to store every person who modifies the record?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in
message
...
Hello,
I was builiding a database for co-worker of mine.
I have a form with drop down box with usernames, Status and
when
status
changes to "Complete" it automatically time stamps.
She wants to keep the history of who touched that record.
Is there anyway to concatenate username, status and time
stamp
to
another
column name "History" and keep adding to that every time
that
record
is
touched by someone? For Example:.
Invoice # Invoice Date Username Status Time
STamp
Hisotry
123 2/15/2005 JOE Complete
1:15pm
Joe
Complete 1:15

Smith Complete 2:35


Or can anyone recommend better solution for this? any help
would
appreciate
it!
Thank you!

THANK YOU SO MUCH!! WORKS WONDERFULLY!! Thank you for all your help and your
patience!

"Lynn Trapp" wrote:

Try changing that query to something like this:

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.*
FROM FL
WHERE FL.ID = Me.txtID

This assumes that you have a control on your form that has FL.ID as it's
control source and you have named that control txtID.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
i have just created a simple append query....

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG
AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.ID, FL.Name, FL.[Inv No], FL.Model, FL.Serial, FL.TY, FL.[ORIG
AMT], FL.[BAL DUE], FL.[INV DATE], FL.[Action Needed], FL.Resolution,
FL.Status, FL.Time_Stamp, FL.[Action User], FL.[Resolution User], FL.Memo
FROM FL

THANK YOU!
"Lynn Trapp" wrote:

Could you post the append query that you have?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Hi,
What code do i need to use to have it append only the record the user
has
touched? I have created a append query, but this will append all the
records
from the main table to history table.
THank you!

"Lynn Trapp" wrote:

Well, you can add the UserName to your field in the AfterUpdate event
of
your form AND immediately append the record to another table with the
same
structure -- a sort of archive table. This will effectively give you a
record of every user who has updated every record in your original
table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
yes, it is so difficult designing this database because there are no
unique
field. The system the team uses, you can key same invoice #, date,
Amnt
and
Serial #'s. So when I pull a report and import into database, I
can't
set
the primary key(s). What would you suggest? Should I just not have
the
history table added ?

"Lynn Trapp" wrote:

Uh Oh! Not a good deal. One of the most important reasons for
having a
relational database is to avoid duplicate data. No, an AutoNumber
field
won't work and, while it would allow you to create the link I
discussed,
it
would not prevent the entry of duplicate invoice numbers and serial
numbers.
It would just hide their existence. If you need unique invoice
numbers
and
serial numbers, then you will need to add something, like a unique
index,
on
those fields to prevent that.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
problem i have is that the table don't have primary key because
there
are
no
unique characters. for example: there are duplicate invoices #'s
and
serial
#'s. I couldn't make anything as primary key. Adding autonumber
will
now
work because it will not be the same number in the history table,
correct?
Thank you!

"Lynn Trapp" wrote:

Then you will need to add a history table that is linked one to
many
to
your main table. You can then set the value to CurrentUser in
the
AfterUpdate event of your data entry form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Store every person who modified the record.

Thank you for your help!

"Lynn Trapp" wrote:

Do you want to simply store the last person who modified the
record
or
do
you want to store every person who modifies the record?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in
message
...
Hello,
I was builiding a database for co-worker of mine.
I have a form with drop down box with usernames, Status and
when
status
changes to "Complete" it automatically time stamps.
She wants to keep the history of who touched that record.
Is there anyway to concatenate username, status and time
stamp
to
another
column name "History" and keep adding to that every time
that
record
is
touched by someone? For Example:.
Invoice # Invoice Date Username Status Time
STamp
Hisotry
123 2/15/2005 JOE Complete
1:15pm
Joe
Complete 1:15

Smith Complete 2:35


Or can anyone recommend better solution for this? any help
would
appreciate
it!
Thank you!

That's great to hear. You are very welcome.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
THANK YOU SO MUCH!! WORKS WONDERFULLY!! Thank you for all your help and
your
patience!

"Lynn Trapp" wrote:

Try changing that query to something like this:

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG
AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.*
FROM FL
WHERE FL.ID = Me.txtID

This assumes that you have a control on your form that has FL.ID as it's
control source and you have named that control txtID.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
i have just created a simple append query....

INSERT INTO tblHistory ( ID, Name, [Inv No], Model, Serial, TY, [ORIG
AMT],
[BAL DUE], [INV DATE], [Action Needed], Resolution, Status, Time_Stamp,
[Action User], [Resolution User], [Memo] )
SELECT FL.ID, FL.Name, FL.[Inv No], FL.Model, FL.Serial, FL.TY,
FL.[ORIG
AMT], FL.[BAL DUE], FL.[INV DATE], FL.[Action Needed], FL.Resolution,
FL.Status, FL.Time_Stamp, FL.[Action User], FL.[Resolution User],
FL.Memo
FROM FL

THANK YOU!
"Lynn Trapp" wrote:

Could you post the append query that you have?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
Hi,
What code do i need to use to have it append only the record the
user
has
touched? I have created a append query, but this will append all the
records
from the main table to history table.
THank you!

"Lynn Trapp" wrote:

Well, you can add the UserName to your field in the AfterUpdate
event
of
your form AND immediately append the record to another table with
the
same
structure -- a sort of archive table. This will effectively give
you a
record of every user who has updated every record in your original
table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
yes, it is so difficult designing this database because there are
no
unique
field. The system the team uses, you can key same invoice #,
date,
Amnt
and
Serial #'s. So when I pull a report and import into database, I
can't
set
the primary key(s). What would you suggest? Should I just not
have
the
history table added ?

"Lynn Trapp" wrote:

Uh Oh! Not a good deal. One of the most important reasons for
having a
relational database is to avoid duplicate data. No, an
AutoNumber
field
won't work and, while it would allow you to create the link I
discussed,
it
would not prevent the entry of duplicate invoice numbers and
serial
numbers.
It would just hide their existence. If you need unique invoice
numbers
and
serial numbers, then you will need to add something, like a
unique
index,
on
those fields to prevent that.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in message
...
problem i have is that the table don't have primary key
because
there
are
no
unique characters. for example: there are duplicate invoices
#'s
and
serial
#'s. I couldn't make anything as primary key. Adding
autonumber
will
now
work because it will not be the same number in the history
table,
correct?
Thank you!

"Lynn Trapp" wrote:

Then you will need to add a history table that is linked one
to
many
to
your main table. You can then set the value to CurrentUser in
the
AfterUpdate event of your data entry form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in
message
...
Store every person who modified the record.

Thank you for your help!

"Lynn Trapp" wrote:

Do you want to simply store the last person who modified
the
record
or
do
you want to store every person who modifies the record?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html


"GEORGIA" wrote in
message
...
Hello,
I was builiding a database for co-worker of mine.
I have a form with drop down box with usernames, Status
and
when
status
changes to "Complete" it automatically time stamps.
She wants to keep the history of who touched that
record.
Is there anyway to concatenate username, status and time
stamp
to
another
column name "History" and keep adding to that every time
that
record
is
touched by someone? For Example:.
Invoice # Invoice Date Username Status
Time
STamp
Hisotry
123 2/15/2005 JOE Complete
1:15pm
Joe
Complete 1:15

Smith Complete 2:35


Or can anyone recommend better solution for this? any
help
would
appreciate
it!
Thank you!