Append single record to another table

[Guest]

Good morning all,
Using Access 2003, I have a form that will search for a specific
record and open that record in edit mode on another form. If the record is
edited I want to insert a copy of the original record into a seperate table
before updating the changes.
I have been researching OldValue Property, OriginalValue,
RecortSetClone etc.; but I could really use some guidance. So far it seems I
would need to select and insert each field. Is there a way to select the
entire record to insert at once? I also want to capture the user and insert
the date into the other table, to create a "paper trail" of edits.

Thank you in advace,
Renee

 

[Lynn Trapp]

I'm not sure why you would want to do that, but you could execute an Insert
query in the BeforeUpdate event of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Thank you for your response Lynn, however the question isn't what is needed
but rather how to efficiently do so.
1. What is the best way to identify that the record has changed?
(If there are no edits, do not insert)
2. Can I select an entire record to insert, or does it have to be one field
at a time?
3. How do I specify the OriginalValue or OldValue is the value to insert
into the other table?

Hope that makes sense Thanks,
Renee

 

[Lynn Trapp]

Thank you for your response Lynn, however the question isn't what is

needed
but rather how to efficiently do so.
1. What is the best way to identify that the record has changed?
(If there are no edits, do not insert)

The BeforeUpdate event of your form will only fire if some change has been
made to the record. So, if you put your call to execute the insert query
there, you should have no problem.

2. Can I select an entire record to insert, or does it have to be one
field
at a time?

Yes, you can insert the entire record. Simply reference the value of each of
the individual controls on your form in the insert query. Here's an example.
Warning: This is untested code.

Dim v_SomeField as String
Dim v_SomeOtherField as Double
Dim v_SQL as String
v_SomeField = Me.SomeTextField
v_SomeOtherField = Me.SomeNumericField
v_SQL = "INSERT INTO YourTable Values ("
v_SQL = v_SQL & v_SomeField
v_SQL = v_SQL & ","
v_SQL = v_SQL & v_SomeOtherField
v_SQL = v_SQL & ");"
DoCmd.RunSQL v_SQL



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html