Copy records from a form to a table

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

Guest

The form I have created lists a row of information based on a specific
criteria in a list box. The form's record source is attached to a Query. I
have a command button that shows the first row in text boxes on the form so I
can add additional information to update the records. However, before I
update the records I would like to copy the information from the original
record to an archive table. The purpose is to maintain an archive of the
original information prior to updating. How do I copy this information from
the form into a archive table?

James E
 
I think the best place to do this might be in the form's Before Update event.
You would have to build the record for the Archive table using the OldValue
property of the form's controls and update the Archive Table.
 
I would probably open the recordset in the Open event of the Form:

Set rstArchive = CurrentDB.OpenRecordSet("ArchiveTableNameHere")

Then, in the Before Update event of the form:

With rstArchive
.AddNew
.FieldOne = Me.txtFld1.OldValue
.FieldTwo = Me.txtFld2.OldValue
.Update
End With
 
Did not like the "rstArchive"

Klatuu said:
I would probably open the recordset in the Open event of the Form:

Set rstArchive = CurrentDB.OpenRecordSet("ArchiveTableNameHere")

Then, in the Before Update event of the form:

With rstArchive
.AddNew
.FieldOne = Me.txtFld1.OldValue
.FieldTwo = Me.txtFld2.OldValue
.Update
End With
Click to expand...
 
I don't particulary like it either, but it was the best I could come up with :)

Actually, you need to Dim the record set at the module level so that all
subs and functions in the form have visibility to it. It goes in General
Declarations at the top of your form module:
Private rstArchive As Recordset 'Recordset to load Actual Data
Now every sub and function in the form module can see it.
 
If I remember right the dim statement would be: DIM Recordset As? I can't
remember if it is a string, or Record, etc. that completes the DIM statement.
Sorry, I have been fighting a sinus infection for the past two weeks, so my
head is not real clear right now. Thanks for your help.
 
Sinus Infection! Oh, My God! I hope it is not a computer virus.

Please see my previous post. I gave complete instructions there. In this
case, you should use the Private instead of the Dim
 
Yes, I remember. Just one more clarification. In your first response you told
me to enter "Set rstArchive = CurrentDB.OpenRecordSet("ArchiveTableNameHere")
and then enter in the Before Update event:
With rstArchive
..addNew
..FieldOne = Me.txtFld1.OldValue
..FieldTwo = Me.txtFld2.OldValue
..Update
End With

Then in your second response concerning the DIM Statement you talk about the
Form Module. Do I create a module, run it in a macros and attach it to the
form? Or just enter it into the code builder directly from the form? What is
the best method? Thanks for your patients.
 
No problem. We don't need any Macros. I never use them. They are really
intended for those who cannot code VBA.

When I say Form Module, I mean the VBA code that is contained within the
form object. The easiest way to do it is to open the form's code module
while you have the form open in design mode. On the main tool bar you will
need to find the code icon. Hard to describe, but just point at icons until
the context help box says code. Click it and it will take you into the VB
Editor with whatever code has been written for the form visible.
In the left combo of the VB Editor select (General)
In the right combo select(Declarations)
Put the following after any Option statements and before any sub or function
statements:

Private rstArchive As Recordset 'Recordset to load Actual Data

It is necessary to use Private instead of Dim and to put it in the General
section of the module so that all code in the form will be able to see it.

Now, in the left combo, select Form and in the right combo select Open
Put this in:

Set rstArchive = CurrentDB.OpenRecordSet("ArchiveTableNameHere")

Replace ArchiveTableNameHere with the name of your table. This will open the
table so we can do our updates

Now select Form in the left combo and Before Update in the left:

With rstArchive
.addNew
.FieldOne = Me.txtFld1.OldValue
.FieldTwo = Me.txtFld2.OldValue
.Update
End With

Replace Fieldxxx and .txtFldn to suit you.

And to complete it, select Form in the Left and Close in the right. Then:

rstArchive.Close
Set rstArchive = Nothing

That should take care of it.
 
After entering the information you provided I ran the compiler and the error
it came up with was in the .Field. I am not sure what I am actually suppose
to replace here. Other than this problem I think it will run fine if you can
be more specific about the .Field.

James E.
 
If this is what you are asking about:
.FieldOne = Me.txtFld1.OldValue
Then remember:
Replace Fieldxxx and .txtFldn to suit you.
.FieldOne should be the name of the first field you want to put data in.
It might be necessary, now that I think of it, to change the syntax to:
!FieldOne = Me.txtFld1.OldValue
 
Back
Top