By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Adding info to a table from another table

P: 2
Dear all,

In this code I am trying to loop through two tables AllDatatable_4 and VR2info, adding GIS coordinates that are associated with sample loactions called VR2 to AllDatatable_4. I am a newcomer to access and have been pulling my hair out over this problem. The code compiles but gives an error message "object required". I believe that I am not looping correctly though the two tables. Is there somebody more experienced who could help ?

Many thanks,

Juliane
Expand|Select|Wrap|Line Numbers
  1. Sub VR2infointoAllDataTable()
  2. Dim strSQL As String
  3. Dim db As DAO.Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Set db = CurrentDb()
  7. Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
  8. Set rs2 = db.OpenRecordset("AllDataTable_4", dbOpenTable)
  9. Dim VR2 As String
  10. Dim VR2number As Integer
  11. Dim Xcoordinate As Single
  12. Dim Ycoordinate As Single
  13. Dim Habitat As String
  14. 'VR2number = rs1(0).Value
  15. 'VR2name1 = rs1(0).Value
  16. 'VR2name2 = rs2(0).Value
  17. rs1.MoveFirst
  18. Do Until rs1.EOF
  19.   rs2.MoveFirst
  20.   Do Until rs2.EOF
  21.    If AllDataTable_4.[VR2] = VR2info.[VR2] Then
  22.    strSQL = "INSERT INTO AllDataTable_4 ([VR2Number],[Xcoordinate],[Ycoordinate],[Habitat])SELECT VR2Number,Xcoordinate,Ycoordinate,Habitat FROM" & VR2info & ";"
  23.    DoCmd.RunSQL strSQL
  24.    End If
  25.    DoCmd.SetWarnings True
  26.   rs2.MoveNext
  27.   Loop
  28. rs1.MoveNext
  29. Loop
  30. rs1.Close
  31. rs2.Close
  32. Set rs1 = Nothing
  33. Set rs2 = Nothing
  34. End Sub
  35.  
Dec 27 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
Dear all,

In this code I am trying to loop through two tables AllDatatable_4 and VR2info, adding GIS coordinates that are associated with sample loactions called VR2 to AllDatatable_4. I am a newcomer to access and have been pulling my hair out over this problem. The code compiles but gives an error message "object required". I believe that I am not looping correctly though the two tables. Is there somebody more experienced who could help ?

Many thanks,

Juliane
Expand|Select|Wrap|Line Numbers
  1. Sub VR2infointoAllDataTable()
  2. Dim strSQL As String
  3. Dim db As DAO.Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Set db = CurrentDb()
  7. Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
  8. Set rs2 = db.OpenRecordset("AllDataTable_4", dbOpenTable)
  9. Dim VR2 As String
  10. Dim VR2number As Integer
  11. Dim Xcoordinate As Single
  12. Dim Ycoordinate As Single
  13. Dim Habitat As String
  14. 'VR2number = rs1(0).Value
  15. 'VR2name1 = rs1(0).Value
  16. 'VR2name2 = rs2(0).Value
  17. rs1.MoveFirst
  18. Do Until rs1.EOF
  19.   rs2.MoveFirst
  20.   Do Until rs2.EOF
  21.    If AllDataTable_4.[VR2] = VR2info.[VR2] Then
  22.    strSQL = "INSERT INTO AllDataTable_4 ([VR2Number],[Xcoordinate],[Ycoordinate],[Habitat])SELECT VR2Number,Xcoordinate,Ycoordinate,Habitat FROM" & VR2info & ";"
  23.    DoCmd.RunSQL strSQL
  24.    End If
  25.    DoCmd.SetWarnings True
  26.   rs2.MoveNext
  27.   Loop
  28. rs1.MoveNext
  29. Loop
  30. rs1.Close
  31. rs2.Close
  32. Set rs1 = Nothing
  33. Set rs2 = Nothing
  34. End Sub
  35.  
Hmm, when you want to loop through the recordset, best first to test for finding records and than process the records like:
Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
  2. if rs1.eof and rs.bof then
  3.      exit sub 'Or any other "no data" action...
  4. endif
  5. 'here a record is present and no move first is needed
  6. While NOT rs1.EOF
  7.    'here do your thing
  8.    rs1.move next 'here go to next record
  9. Wend
  10. set rs1 = nothing
  11.  
For adding rows in the other table you can use:
Expand|Select|Wrap|Line Numbers
  1.    rs2.addnew
  2.    rs2!Field1txt = 'Test12345'
  3.    rs2!Field2num = 564
  4.    rs2!Field3Date = #1/1/2008#
  5.    rs.update
  6.  
As you can see, using rs2! will allow the use of the fieldnames of the recordset, for rs1 the same can be applied. The (0) you use would need: rs1.fields(0) to get a value available, but I prefer fieldnames :-)

Clearer ?

Nic;o)
Dec 28 '07 #2

P: 2
Dear Nico,

many thanks for your reply. I am working through it right now.

Juliane
Jan 6 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.