follow up post:

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

Guest

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
 
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
 
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.
 
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....
 
Sorry, I misunderstood what you wanted.

When you set rstForm = Me.RecordsetClone, the recordset (rstForm) *usually*
opens with the first record as the current record; the current record could
be any record. so you have to find the record in rstForm, then copy it.

Also, it is really a good idea to check the .NoMatch property (I added the
lines) to make sure the record was found before the copy.

In the code there is a line:

rstForm.FindFirst "[TestID] = " & Me![lngTestID]

Change [TestID] to the name of the primary key field in the recordset
"rstForm".
Change [lngTestID] to the name of the control on the form that has the
primary key field as the control source.

NOTE: the name of the control should be differend than the field name!!!!


'---- Begin New Code ----------------
Private Sub Command0_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)

' locate the record in recordsetclone that is displayed in the form
' edit the following line:
' change [TestID] to the name of your primary field in the
recordsetclone
' change [lngTestID] to the name of the CONTROL on the form that has
' the primary field as the control source.
rstForm.FindFirst "[TestID] = " & Me![lngTestID]

'check that the record was located
If Not rstForm.NoMatch Then
'copy the fields across
With rstArchive
.AddNew
For intField = 0 To (rstForm.Fields.Count - 1)
.Fields(intField) = rstForm.Fields(intField)
Next
.Update
End With
Else
' something went wrong.....
MsgBox "ERROR - Record not found!!!"
End If

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

rstForm.Close
Set rstForm = Nothing

Set dbs = Nothing

End Sub
'------------End Code ----------------


Steve
 
Back
Top