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!
:
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
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!
:
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
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 ?
:
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
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!
:
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
message
Store every person who modified the record.
Thank you for your help!
:
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
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!
