asked on

1)Populating a table with values from another table?

I have a access table xyz with 3 fields (accno,organisation,date).I have another table with 73 fields.I want to fill in this table with accno from the previous table.Note tht accno is primary key of both tables.I wrote the foll. code but it gives me a runtime error saying no current record.
Dim wrkJet As DAO.Workspace
Dim dbJet As DAO.Database
Dim rsJet As DAO.Recordset
Dim strLocation As String
Dim rsJet1 As DAO.Recordset
strLocation = "C:\Documents and Settings\Administrator\My Documents\BLOOD TYPING FILES\"
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbJet = wrkJet.OpenDatabase(strLocation & "blood.mdb")
Set rsJet = dbJet.OpenRecordset("SELECT * FROM bloodlabwork ")
Set rsJet1 = dbJet.OpenRecordset("SELECT * FROM testResults1 ")
While Not rsJet.EOF
rsJet1.AddNew
rsJet1("access_no") = rsJet("accession_number")

rsJet.MoveNext
rsJet1.MoveNext
Wend
rsJet.Close
dbJet.Close
wrkJet.Close
rsJet1.Close

Please tell me if I'm going wrong somewhere..
thanks
Adriana

Arthur_Wood

try making thses changes:

Do While Not rsJet.EOF
rsJet1.AddNew
rsJet1("access_no") = rsJet("accession_number")
rsJet1.Update
rsJet.MoveNext

Loop

Artur Wood

homelesspew

ASKER

The above modification has filled up the table with what I wanted but i get an error
"objedt no longer valid"
The debugger points to rsjet1.close.
any suggestions.....

homelesspew

ASKER

btw I used the while loop and not the do loop.

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

homelesspew

ASKER

Thanx a lot!
Adriana

Arthur_Wood

the While....Wend form of the Loop is now considered to be "OLD" and is not recommended by Microsoft. That syntax is NOT supported in VB.NET for instance.

the form :

Do While (condition) ... loop

is Much better from a design perspective

But for the present case, it mkes no FUNCTIONAL difference.

Arthur Wood