Transferring Records From 1 Table to Another?

[JonnyCab]

What is the easiest way to transfer a record from one table to another. This is the code I am using at the moment which seems pretty useless. There must be a way of transferring the who record rather than, transferring one field at a time to the new table then deleting the source record.

With DataEnvironment1
.rscmdTemp.Open
.rscmdRCode.Open
.rscmdError.Open

Do While Not .rscmdTemp.EOF
.rscmdRCode.MoveFirst
.rscmdRCode.Find ("ResourceCode = '" & .rscmdTemp!Resource & "'")
If .rscmdRCode.EOF Then
.rscmdError.AddNew
.rscmdError!Contract = .rscmdTemp!Contract
.rscmdError!Resource = .rscmdTemp!Resource
.rscmdError!Employee = .rscmdTemp!Employee
.rscmdError!EntryDate = .rscmdTemp!EntryDate
.rscmdError!WComponent = .rscmdTemp!WComponent
.rscmdError!Hours = .rscmdTemp!Hours
.rscmdError!Breakeven = .rscmdTemp!Breakeven
.rscmdError!Sell = .rscmdTemp!Sell
.rscmdError.Update
.rscmdTemp.Delete
'.rscmdTemp.Update
End If
.rscmdTemp.MoveNext

Loop

.rscmdRCode.Close
.rscmdTemp.Close
.rscmdError.Close
End With



Any help would be appreciated

Cheers

Jonny

[Bigley]

You could just use a SQL string insert statement - build it using the values from your recordset and loop around it, moving next through your recordset

[JonnyCab]

This is what I ended up using...let me know if there is any better way.

Dim strSQL As String
Dim Temp As New ADODB.Recordset
Dim fld As ADODB.Field
Dim i As Integer

i = 0

With DataEnvironment1
.rscmdTemp.Open
.rscmdRCode.Open
.rscmdError.Open

Do While Not .rscmdTemp.EOF
.rscmdRCode.MoveFirst
.rscmdRCode.Find ("ResourceCode = '" & .rscmdTemp!Resource & "'")
If .rscmdRCode.EOF Then
.rscmdError.AddNew
'This For/Next loop does the trick.
For Each fld In .rscmdTemp.Fields
.rscmdError(i) = fld.Value
i = i + 1
Next

.rscmdError.Update
.rscmdTemp.Delete
End If
.rscmdTemp.MoveNext

Loop

.rscmdRCode.Close
.rscmdTemp.Close
.rscmdError.Close
End With