getrow from 2 tables and past to another table

[EBee]

Hi,
I need some direction on how to grab an entire row from two tables with certain condition. Then, put both of them to a new table.

rst1
rst2

Do while rst1.EOF and rst2.EOF
if. . . then
need code here------ (rst1.getrow . . . insert to tablenew and rst2.getrow and insert to tablenew. ..

end if
rst1.movenext
rst2.movenext
Loop

End Sub

thanks in advance

 

[Aivars]

dim rst1 as recordset
dim rst2 as recordset
dim rst3 as recordset

Do while rst1.EOF and rst2.EOF
if. . . then
need code here------ (rst1.getrow . . . insert to tablenew and rst2.getrow and insert to tablenew. ..
rst3.addnew
rst3!getrow1=rst1.getrow
rst3!getrow2=rst2.getrow
rst3.update
end if
rst1.movenext
rst2.movenext
Loop

End Sub

Aivars

 

[EBee]

I am getting an compile error
" Method or data members not found" error msg.
what am i doing wrong??


Private Sub LoopBuildfile()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstNew As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("auxil1&quot
Set rstOpen = db.OpenRecordset("Auxil_Logic_Open&quot

Do While rstOpen.EOF

If (rst!Trans_Number) = "6" Then
If (rst!Number) = (rstOpen!Number) Then
If (DateDiff(&quot;s&quot;, rstOpen!Tx_Time, rst!Tx_Time) < 60) Then
rstNew.AddNew
rstNew!getrow = rst.getrow
rstNew!getrow = rstOpen.getrow
rstNew.Update
End If
End If
End If
rstOpen.MoveNext
Loop

End Sub

 

[Aivars]

Why didn't you set recordset rstNew?

Aivars

 

[EBee]

Yes, i added the recordset. . but still won't work. is the 1 needed in the getrow1 and getrow2 ??? will not run

erwin

 

[Aivars]

Ervin,
the rst stays on first record during whole process. You may use rst.findfirst &quot;.....&quot; or rst.movenext - I don't know what data you want to add...

Aivars

 

[EBee]

My code is not going to the first record and not going to the next record. . please help .. i tried to place the .MoveNext or .MoveFirst all around and not having any luck at all. . .

thanks
erwin. . . help

Private Sub Buildfile()



Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstNew As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;auxil1&quot
Set rstOpen = db.OpenRecordset(&quot;Auxil_Logic_Open&quot
Set rstNew = db.OpenRecordset(&quot;table6&quot


rst.MoveFirst
rstOpen.MoveFirst

If rstOpen!Number = &quot;0&quot; And rst!Number = &quot;0&quot; Then

Beep
Beep
Beep
End
Else

Do While rst.EOF And rstOpen.EOF

rst.MoveFirst
rstOpen.MoveFirst


If rst!Number = rstOpen!Number Then
If rst!Trans_Number = &quot;6&quot; Then
If rst!Tx_Date = rstOpen!Tx_Date Then
If (DateDiff(&quot;s&quot;, rstOpen!Tx_Time, rst!Tx_Time) < 60) Then

rstNew.AddNew
rstNew!GetRows = rst.GetRows
rstNew!GetRows = rstOpen.GetRows
rstNew.Update

End If
End If

End If

End If
rst.MoveNext

Loop
End If

End Sub

 

[Aivars]

Wrong commands' place.
Each time in Do cicle you send recordset's cursor to first record:

Do While rst.EOF And rstOpen.EOF
'You move cursor to first each time after you was moving it next
rst.MoveFirst
rstOpen.MoveFirst

If rst!Number = rstOpen!Number Then 'Here you analize the first record's values always
If rst!Trans_Number = &quot;6&quot; Then


Write so:

rst.MoveFirst
rstOpen.MoveFirst
Do While rst.EOF And rstOpen.EOF
If rst!Number = rstOpen!Number Then
.........

Aivars

 

[Gzep]

Hmmm.. if I may interject...

Usually i use:

rs.movefirst
do while NOT rs.EOF 'note the NOT...
<do some stuff>
rs.MoveNext
loop

if the data comes from two tables, use a query to merge them
and open the query as your source recordset.

Gzep, Soldier of Fortune. :

 

[EBee]

the problem i have is that i am trying to find a match of ALL the records in rstOpen with all of rst. So, the first record in rstOpen must be compared to all the records in rst first, and if no record match with the condition set, then go to the second record in rstOpen and compare the record with all the records in rst.

is my code doing this or am I approaching this the wrong way??

thanks

erwin