Append single record to another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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)
Click to expand...

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?
Click to expand...

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
 
Back
Top