Table History

  • Thread starter Thread starter George
  • Start date Start date
G

George

Is it possible to have the data from a form go to two
tables - one the table that created the form and another
identical table I want to use for historical purposes. I
would like to be able to delete the data in the form and
still have a historical copy.
Thanks - George
 
I would like each new line of info that is added to be
added to the back-up table. And when the original line is
deleted the back-up table is untouched.
Thanks - George
 
So, you want to trace *inserts* only? If the record is changed later before
it is deleted, you don't need to know about that.

In that case, execute an Append query statement in the AfterInsert event
procedure of your form. If you make a dummy insert query (Append on Query
menu in Query design), you can then switch it to SQL View (View menu) and
copy the statement you need. Then use the Execute or RunSQL method to
actually write the record to your duplicate table.
 
Allen - Thanks for your time but your way over my head..
Would this work ?
On the form I enter the data I have the following code
added to the afterupdate command

Private Sub Combo12_AfterUpdate()
Me![Last Name] = Me![Combo12].Column(1)
End Sub


Private Sub Combo14_AfterUpdate()
Me![Description] = Me![Combo14].Column(1)
Me![Insp-Due-Date] = Me![Combo14].Column(2)
End Sub

Question is can I insert the name of the other table and
copy to it at the same time.

Thanks - George

-----Original Message-----
Issue 1:
DoCmd.SetWarnings will prevent the message. Don't forget to turn it back on
again.

Issue 2:
You need to execute a query statement with a Critieria that matches the
primary key value of the record in the form. You could set the Criteria row
under your primary key field in the query to:
[Forms]![MyForm]![MyID]

Actually you can solve both problems at once if you Execute a query
statement that includes the primary key value from the form:
Dim strSQL As String
strSQL = "INSERT INTO ... WHERE MyID = " & Me.MyID & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
As mentioned earlier, use the SQL View of the append query to help you get
the insert query statement you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

George said:
Allen - I created the Append Query and it works but it has
created its own problems.
1. I inserted the query in the Afterinsert and it works
but it stops and gives you a message that you are about to
copy files then another message how many files copied.
2. But worst then that it copies all the lines in the form
each time. If I add one new line of info to my form and
there's already 100 lines in it 101 lines gets added to my
history table.

George

query
(Append on Query View
(View menu) and line
is time
it code
to form
and
Click to expand...


.
Click to expand...
 
No. You cannot insert a record into another table like that.

Use the After Insert event procedure of the *form*, not that of the combos.
If you have an Append query that is inserting records, you are most of the
way there. It's just a matter of firing it in Form_AfterInsert, and limiting
it to the one record in the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")
George said:
Allen - Thanks for your time but your way over my head..
Would this work ?
On the form I enter the data I have the following code
added to the afterupdate command

Private Sub Combo12_AfterUpdate()
Me![Last Name] = Me![Combo12].Column(1)
End Sub


Private Sub Combo14_AfterUpdate()
Me![Description] = Me![Combo14].Column(1)
Me![Insp-Due-Date] = Me![Combo14].Column(2)
End Sub

Question is can I insert the name of the other table and
copy to it at the same time.

Thanks - George

-----Original Message-----
Issue 1:
DoCmd.SetWarnings will prevent the message. Don't forget to turn it back on
again.

Issue 2:
You need to execute a query statement with a Critieria that matches the
primary key value of the record in the form. You could set the Criteria row
under your primary key field in the query to:
[Forms]![MyForm]![MyID]

Actually you can solve both problems at once if you Execute a query
statement that includes the primary key value from the form:
Dim strSQL As String
strSQL = "INSERT INTO ... WHERE MyID = " & Me.MyID & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
As mentioned earlier, use the SQL View of the append query to help you get
the insert query statement you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

George said:
Allen - I created the Append Query and it works but it has
created its own problems.
1. I inserted the query in the Afterinsert and it works
but it stops and gives you a message that you are about to
copy files then another message how many files copied.
2. But worst then that it copies all the lines in the form
each time. If I add one new line of info to my form and
there's already 100 lines in it 101 lines gets added to my
history table.

George


-----Original Message-----
So, you want to trace *inserts* only? If the record is
changed later before
it is deleted, you don't need to know about that.

In that case, execute an Append query statement in the
AfterInsert event
procedure of your form. If you make a dummy insert query
(Append on Query
menu in Query design), you can then switch it to SQL View
(View menu) and
copy the statement you need. Then use the Execute or
RunSQL method to
actually write the record to your duplicate table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has
spurious "_SpamTrap")
I would like each new line of info that is added to be
added to the back-up table. And when the original line
is
deleted the back-up table is untouched.
Thanks - George

-----Original Message-----
You could use the AfterUpdate event procedure of the
form
to write the
record to the other table as well.

Did you want to insert a copy of the record each time
it
is changed? Or did
you just want to keep the most recent values before it
was deleted? The
programming is not for the faint hearted, but the code
to
keep a complete
audit trail of every insert, edit, and delete is
available in this link:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has
spurious "_SpamTrap")

Is it possible to have the data from a form go to two
tables - one the table that created the form and
another
identical table I want to use for historical
purposes. I
would like to be able to delete the data in the form
and
still have a historical copy.
Thanks - George


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top