follow up post:

P

paul

Can anyone help me follow up this post:

the code only copies one record. the suggestion was to change the:

set= rstForm me.recordsetclone
to
set= rstForm me.recordset

unfortunately im using 97 and i dont get an option me.recordset?

can anyone help please?


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
 
S

SteveS

Paul,

I saw a few things wrong; I think this will work.

*** > set= rstForm me.recordsetclone should be set rstForm =
me.recordsetclone
('=' in the wrong place)

*** missing line: rstForm.MoveNext

*** needed a way to stop when the end of rstForm recordset reached. Added
Do While...Loop lines

*** closed and destroyed the objects that were created
'-----begin code--------------------------------------------------
Private Sub Button_Click()
Dim rstForm As DAO.Recordset, rstArchive As DAO.Recordset
Dim dbs As DAO.Database
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)

rstForm.MoveFirst
Do While Not rstForm(EOF)
'copy the fields across
With rstArchive
.AddNew
For intField = 0 To (rstForm.Fields.Count - 1)
.Fields(intField) = rstForm.Fields(intField)
Next
.Update
End With
' move to the next record in rstForm
rstForm.MoveNext
Loop

' also need to clean up
rstArchive.Close
Set rstArchive = Nothing

rstForm.Close
Set rstForm = Nothing

Set dbs = Nothing

End Sub
'-----end code--------------------------------------------------


Steve
 
S

SteveS

Two more items...

The subroutine doesn't limit the records that are copied from recordset
rstForm, so everytime the Sub is run, ALL records get copied again. You need
to come up with a way to select records (ie after a date), have a field in a
table the is set (or cleared) that indicates record copy status, or delete
the records after they have been copied to the archive table.
 
P

paul

thanks steve, unfortuately im not looking to copy every recored, i only want
to copy the 1 record which is displayed in on the form at that particular
time.

the oringal code copies only record 1 of the table every time irrespective
of which record is diplayed on the form. what i need to do is update the
code to recognise the record on the form as the record that needs to be
copied and not the 1st record in the table or all records...

any other thoughts, grateful for any assistance....
 
Top