Copying usng a recordset

[Guest]

Hi,
i have a form which facilitates the user to edit the records in a subform.

I have an undo method, which can either undo the current record, or wipe any
changes and restore the table to the point at which the form was opened. i.e
this is what i have in the form_Load procedure

Set thisdb = CurrentDb
strSQL = "SELECT * FROM CONNECTIVITY_DATA_" & boatnum & " WHERE CKT =
'" & CKT & "';"


Me.frmConnectivityData.Form.RecordSource = strSQL 'fill the subform
Me.frmConnectivityData.Form.Requery

Set rstBackup = thisdb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
rstBackup.MoveLast

boatnum is a number (i have different tables with the name only differing by
the number, and CKT is a field in these tables.

In the undo method, i would have something like
(actual columns arent called DATA!)...

'open a recordset that has the current records in it (not shown)
with rstActual
.edit
'add new / edit data
![DATA1] = rstBackup![DATA1]
![DATA2] = rstBackup![DATA2]
'approx 20 more
..update

end with


As i am doing a straight copy between recordsets, it seems tedious to refer
to each field in turn each time i want to call this method. (this could be
hundreds of times if i want to reset the whole table to its original state)

is there a way that i could use the contents of a recordset in an sql
statement?

other than that, is there a way to mass copy records from one recordset to
another?

 

[Guest]

I have worked it out. all i need to do is loop over the columns in the data
by using the fields array. i.e

i = 0

Do While i < fromCopySet.Fields.count

If Not IsNull(fromCopySet.Fields(i)) Then




toCopySet.edit
toCopySet.Fields(i) = fromCopySet.Fields(i)
toCopySet.Update
End If
i = i + 1
loop

hope this helps someone.
cheers,
Ben

 

[George Nicholson]

In this case, that should work. However, *if* you had 2 recordsets where
there was the slightest chance that the fields might not always be in the
exact same identical order you would be better off incorporating fieldnames
rather than index position alone:

toCopySet.Fields(fromCopySet.Fields(i).Name) = fromCopySet.Fields(i)

You could also use a For..Each loop and not worry about Index position:

For Each fld in toCopySet.Fields
If Not IsNull(fld) Then
toCopySet.edit
toCopySet.Fields(fld.Name) = fromCopySet.Fields(fld.Name)
toCopySet.Update
End If
Loop

BTW, are you sure about the Not IsNull test? What if user made a field Null
and wants to roll it back to its original non-Null value? I would think
that if you want to rollback, then rollback everything without any second
guessing.

HTH,

I have worked it out. all i need to do is loop over the columns in the data
by using the fields array. i.e

i = 0

Do While i < fromCopySet.Fields.count

If Not IsNull(fromCopySet.Fields(i)) Then




toCopySet.edit
toCopySet.Fields(i) = fromCopySet.Fields(i)
toCopySet.Update
End If
i = i + 1
loop

hope this helps someone.
cheers,
Ben

Hi,
i have a form which facilitates the user to edit the records in a
subform.

I have an undo method, which can either undo the current record, or wipe
any
changes and restore the table to the point at which the form was opened.
i.e
this is what i have in the form_Load procedure

Set thisdb = CurrentDb
strSQL = "SELECT * FROM CONNECTIVITY_DATA_" & boatnum & " WHERE CKT
=
'" & CKT & "';"


Me.frmConnectivityData.Form.RecordSource = strSQL 'fill the subform
Me.frmConnectivityData.Form.Requery

Set rstBackup = thisdb.OpenRecordset(strSQL, dbOpenDynaset,
dbReadOnly)
rstBackup.MoveLast

boatnum is a number (i have different tables with the name only differing
by
the number, and CKT is a field in these tables.

In the undo method, i would have something like
(actual columns arent called DATA!)...

'open a recordset that has the current records in it (not shown)
with rstActual
.edit
'add new / edit data
![DATA1] = rstBackup![DATA1]
![DATA2] = rstBackup![DATA2]
'approx 20 more
.update

end with


As i am doing a straight copy between recordsets, it seems tedious to
refer
to each field in turn each time i want to call this method. (this could
be
hundreds of times if i want to reset the whole table to its original
state)

is there a way that i could use the contents of a recordset in an sql
statement?

other than that, is there a way to mass copy records from one recordset
to
another?