Adding info to a table from another table

[JStruve]

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

Sub VR2infointoAllDataTable()
Dim strSQL As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
Set rs2 = db.OpenRecordset("AllDataTable_4", dbOpenTable)
Dim VR2 As String
Dim VR2number As Integer
Dim Xcoordinate As Single
Dim Ycoordinate As Single
Dim Habitat As String
'VR2number = rs1(0).Value
'VR2name1 = rs1(0).Value
'VR2name2 = rs2(0).Value
rs1.MoveFirst
Do Until rs1.EOF
  rs2.MoveFirst
  Do Until rs2.EOF
   If AllDataTable_4.[VR2] = VR2info.[VR2] Then
   strSQL = "INSERT INTO AllDataTable_4 ([VR2Number],[Xcoordinate],[Ycoordinate],[Habitat])SELECT VR2Number,Xcoordinate,Ycoordinate,Habitat FROM" & VR2info & ";"
   DoCmd.RunSQL strSQL
   End If
   DoCmd.SetWarnings True
  rs2.MoveNext
  Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub
 

[nico5038]

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

Sub VR2infointoAllDataTable()
Dim strSQL As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
Set rs2 = db.OpenRecordset("AllDataTable_4", dbOpenTable)
Dim VR2 As String
Dim VR2number As Integer
Dim Xcoordinate As Single
Dim Ycoordinate As Single
Dim Habitat As String
'VR2number = rs1(0).Value
'VR2name1 = rs1(0).Value
'VR2name2 = rs2(0).Value
rs1.MoveFirst
Do Until rs1.EOF
  rs2.MoveFirst
  Do Until rs2.EOF
   If AllDataTable_4.[VR2] = VR2info.[VR2] Then
   strSQL = "INSERT INTO AllDataTable_4 ([VR2Number],[Xcoordinate],[Ycoordinate],[Habitat])SELECT VR2Number,Xcoordinate,Ycoordinate,Habitat FROM" & VR2info & ";"
   DoCmd.RunSQL strSQL
   End If
   DoCmd.SetWarnings True
  rs2.MoveNext
  Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub
 

Hmm, when you want to loop through the recordset, best first to test for finding records and than process the records like:

Set rs1 = db.OpenRecordset("VR2info", dbOpenTable)
if rs1.eof and rs.bof then
     exit sub 'Or any other "no data" action...
endif
'here a record is present and no move first is needed
While NOT rs1.EOF
   'here do your thing
   rs1.move next 'here go to next record
Wend
set rs1 = nothing
 

For adding rows in the other table you can use:

   rs2.addnew
   rs2!Field1txt = 'Test12345'
   rs2!Field2num = 564
   rs2!Field3Date = #1/1/2008#
   rs.update
 

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)

[JStruve]

Dear Nico,

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

Juliane