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
[code=vb]
Sub VR2infointoAllD ataTable()
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.OpenRecordse t("VR2info", dbOpenTable)
Set rs2 = db.OpenRecordse t("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,Xcoor dinate,Ycoordin ate,Habitat FROM" & VR2info & ";"
DoCmd.RunSQL strSQL
End If
DoCmd.SetWarnin gs True
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub
[/code]

[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
[code=vb]
Sub VR2infointoAllD ataTable()
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.OpenRecordse t("VR2info", dbOpenTable)
Set rs2 = db.OpenRecordse t("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,Xcoor dinate,Ycoordin ate,Habitat FROM" & VR2info & ";"
DoCmd.RunSQL strSQL
End If
DoCmd.SetWarnin gs True
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub
[/code]

Hmm, when you want to loop through the recordset, best first to test for finding records and than process the records like:
[code=vb]
Set rs1 = db.OpenRecordse t("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
[/code]

For adding rows in the other table you can use:
[code=vb]
rs2.addnew
rs2!Field1txt = 'Test12345'
rs2!Field2num = 564
rs2!Field3Date = #1/1/2008#
rs.update
[/code]
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