Copy Record

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

Guest

Hi,

ive created a form that displays a record. I need to be able to copy the
entire record in to another table. Ideally, i want the code to sit behind a
button.

the form im talking about draws its record from a table called tblMain and i
want to copy the record on display to a table called tblMainArchive, can
anyone help me with the code to do this please...

paul
 
Try

Private Sub Button_Click()
dim rstForm as dao.recordset, dbs as dao.Database, rstArchive as _
dao.recordset
dim intField as integer

'get a clone of the forms recordset
set rstForm = me.recordsetclone
'get a recordset for your archive table
set dbs = currentDb
set rstArchive = dbs.OpenRecordset("ArchiveTable", dbOpenDynaset)

'copy the fields across
With rstArchive
.AddNew
For intField = 0 to (rstForm.Fields.Count - 1)
.Fields(intField) = rstForm.Fields(intField)
Next
.Update
End With

End Sub

This will only work if the structure of the ArchiveTable is the same
as the forms source table/query. You could also add in an extra fields
to the ArchiveTable to log the date it was archived, or any other
relevent info.
 
You can probably do it as described, but I doubt very much that you want to.
Duplicate records are usually not good. An exception might be double data
entry for checking accuracy of critical records, but in such a case a query
is periodically run to remove duplicates, and in any case that doesn't sound
like your situation. If you need to archive certain records, why do you want
to keep them in the main table as well? Wouldn't you be better off with a
query? If you want to archive records older that a year or whatever, a Make
Table Query is almost certainly a better choice.
 
Dave thanks,, your code worked but it only copies the first record in the
table and not the record that im viewing... any other thoughts on how i get
it to copy the record that im viewing ?

Paul
 
Dave thanks,, your code worked but it only copies the first record in the
table and not the record that im viewing... any other thoughts on how i get
it to copy the record that im viewing ?

Paul
 
If you change the RecordsetClone to Recordset it works.

I thought it would work with RecordsetClone the same. Reading the help
for RecordsetClone suggests that it should - maybe someone else can
explain why it doesn't.
 
Dave, i tried to change RecordsetClone to Recordset and it didnt work. it
could be because im using access 97 and when i type in Me. there isnt a
recordset option to choose? any other thoughts

regards,

paul
 
After the line

set rstForm = me.recordsetclone

put rstForm.Bookmark = me.Bookmark

I am using Access 2000, which may be why the Me.Recordset works for
me.
 
Back
Top