1)Populating a table with values from another table?

homelesspew
homelesspew used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try making thses changes:

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

Loop

Artur Wood

Author

Commented:
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.....

Author

Commented:
btw I used the while loop and not the do loop.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

change the order in which you close the objects:

instead of this way:

dbJet.Close
wrkJet.Close
rsJet1.Close


make it:
rsJet1.Close
dbJet.Close
wrkJet.Close

you ALWAYS close in the REVERSE order that you opened or created.

Arthur Wood

Author

Commented:
Thanx a lot!
Adriana
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial