read a recordset in one table and transfer it to next

[kavya]

I have a question I have a table1 and table2 . Table1 has the field 'trandate', right now table2 is empty without any data in it. I am trying to read and loop through all the records in recordset and then transfer it to table2. Here is my code.
I am missing something. Please help me very urgent.

Thanks a lot.
Public Function convertdate()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("table1", dbOpenDynaset)
rs1.MoveFirst
If Not rs1.EOF Then
Set rs2 = db.OpenRecordset("table2", dbOpenDynaset)
Do Until rs1.EOF


With rs1
.AddNew
.Fields("trandate&quot = rs2.Fields("trandate&quot
.Update
End With
Loop
End If
rs1.MoveNext

End Function

Thanks again

 

[LarryDeLaruelle]

Kavya:

Try this:

Public Function convertdate()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("table1", dbOpenDynaset)
rs1.MoveFirst

Do Until rs1.EOF
With rs2
.AddNew
!trandate = rs1!trandate
.Update
End With
rs1.MoveNext
Loop

End Function

This is set up with rs1 as the table with the data and rs2 as the table to receive the data.

If you use the band (!) you can use the actual field name. If you use .Fields(), you must specify the field position from base 0. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kavya]

hi,
Thanks for your reply, I tried it but it is still giving error at the line
.add new.
saying the object does'nt support.
And one more question is what about rs2.
You did'nt define it anywhere so how would the table know which table to receive.

Thanks a lot

 

[LarryDeLaruelle]

Kavya:

Sorry, forgot that. Try this:

Public Function convertdate()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("table1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("table2&quot
rs1.MoveFirst

Do Until rs1.EOF
With rs2
.AddNew
!trandate = rs1!trandate
.Update
End With
rs1.MoveNext
Loop

End Function Larry De Laruelle
ldelaruelle@familychildrenscenter.org