im trying to iterate through 2 recordsets.

the 2 recordsets contain data from 2 tables with a 1(rs1) to many (rs2)
relationship.

so the idea is for each loop of rs1 there should be serveral loops of rs2.

but im stuck inside my loops and cant seem to find a way out.

help please

Set rs1 = CurrentDb.OpenRecordset(qry1)
'loop through transaction codes and check if items have been returned
If Not (rs1.BOF And rs1.EOF) Then
rs1.MoveFirst
Do While Not rs1.EOF
'set closed to catch open record
closed = True
'collect transcode from recordset and search transdata
tcode = rs1.Fields("transcode")
qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM Transdata
WHERE Transdata.Transcode= " & tcode & ";"
Set rs2 = CurrentDb.OpenRecordset(qry2)
If Not (rs2.BOF And rs2.EOF) Then
rs2.MoveFirst
'loop through transdata for each transaction code, set closed to
true if open record found
Do While Not rs2.EOF
If IsNull(rs2.Fields("in")) Then
closed = False
rs2.MoveLast
Else
rs2.MoveNext
End If
Loop
End If
If closed = True Then
rs1.Edit
rs1.Fields("in date") = Date
rs1.Update
rs1.MoveNext
End If
Loop
End If

Hi Joseph Atie,

you movenext the rs1 just when closed= true but if IsNull(rs2.Fields("in"))
you put closed =false and you movelast the rs2 so you end this loop and so
you don't movenext the rs1 and you enter again the first loop with the same
value for tcode and so the rs2 will be the same so you'll never end this
loop. You have to check the exit logic of your loop.

HTH Paolo

"Joseph Atie" wrote:

> im trying to iterate through 2 recordsets.
>
> the 2 recordsets contain data from 2 tables with a 1(rs1) to many (rs2)
> relationship.
>
> so the idea is for each loop of rs1 there should be serveral loops of rs2.
>
> but im stuck inside my loops and cant seem to find a way out.
>
> help please
>
> Set rs1 = CurrentDb.OpenRecordset(qry1)
> 'loop through transaction codes and check if items have been returned
> If Not (rs1.BOF And rs1.EOF) Then
> rs1.MoveFirst
> Do While Not rs1.EOF
> 'set closed to catch open record
> closed = True
> 'collect transcode from recordset and search transdata
> tcode = rs1.Fields("transcode")
> qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM Transdata
> WHERE Transdata.Transcode= " & tcode & ";"
> Set rs2 = CurrentDb.OpenRecordset(qry2)
> If Not (rs2.BOF And rs2.EOF) Then
> rs2.MoveFirst
> 'loop through transdata for each transaction code, set closed to
> true if open record found
> Do While Not rs2.EOF
> If IsNull(rs2.Fields("in")) Then
> closed = False
> rs2.MoveLast
> Else
> rs2.MoveNext
> End If
> Loop
> End If
> If closed = True Then
> rs1.Edit
> rs1.Fields("in date") = Date
> rs1.Update
> rs1.MoveNext
> End If
> Loop
> End If

What Paolo is trying to say is that your last few lines should look like:

If closed = True Then
rs1.Edit
rs1.Fields("in date") = Date
rs1.Update
End If
'the following line was move from inside the If/EndIf to outside it
rs1.MoveNext
Loop
End If

----
HTH
Dale



"Paolo" wrote:

> Hi Joseph Atie,
>
> you movenext the rs1 just when closed= true but if IsNull(rs2.Fields("in"))
> you put closed =false and you movelast the rs2 so you end this loop and so
> you don't movenext the rs1 and you enter again the first loop with the same
> value for tcode and so the rs2 will be the same so you'll never end this
> loop. You have to check the exit logic of your loop.
>
> HTH Paolo
>
> "Joseph Atie" wrote:
>
> > im trying to iterate through 2 recordsets.
> >
> > the 2 recordsets contain data from 2 tables with a 1(rs1) to many (rs2)
> > relationship.
> >
> > so the idea is for each loop of rs1 there should be serveral loops of rs2.
> >
> > but im stuck inside my loops and cant seem to find a way out.
> >
> > help please
> >
> > Set rs1 = CurrentDb.OpenRecordset(qry1)
> > 'loop through transaction codes and check if items have been returned
> > If Not (rs1.BOF And rs1.EOF) Then
> > rs1.MoveFirst
> > Do While Not rs1.EOF
> > 'set closed to catch open record
> > closed = True
> > 'collect transcode from recordset and search transdata
> > tcode = rs1.Fields("transcode")
> > qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM Transdata
> > WHERE Transdata.Transcode= " & tcode & ";"
> > Set rs2 = CurrentDb.OpenRecordset(qry2)
> > If Not (rs2.BOF And rs2.EOF) Then
> > rs2.MoveFirst
> > 'loop through transdata for each transaction code, set closed to
> > true if open record found
> > Do While Not rs2.EOF
> > If IsNull(rs2.Fields("in")) Then
> > closed = False
> > rs2.MoveLast
> > Else
> > rs2.MoveNext
> > End If
> > Loop
> > End If
> > If closed = True Then
> > rs1.Edit
> > rs1.Fields("in date") = Date
> > rs1.Update
> > rs1.MoveNext
> > End If
> > Loop
> > End If

Thanx Dale,
your explanation is more readable than mine but well, I wrote it this
morning before my coffee...

"Dale Fye" wrote:

> What Paolo is trying to say is that your last few lines should look like:
>
> If closed = True Then
> rs1.Edit
> rs1.Fields("in date") = Date
> rs1.Update
> End If
> 'the following line was move from inside the If/EndIf to outside it
> rs1.MoveNext
> Loop
> End If
>
> ----
> HTH
> Dale
>
>
>
> "Paolo" wrote:
>
> > Hi Joseph Atie,
> >
> > you movenext the rs1 just when closed= true but if IsNull(rs2.Fields("in"))
> > you put closed =false and you movelast the rs2 so you end this loop and so
> > you don't movenext the rs1 and you enter again the first loop with the same
> > value for tcode and so the rs2 will be the same so you'll never end this
> > loop. You have to check the exit logic of your loop.
> >
> > HTH Paolo
> >
> > "Joseph Atie" wrote:
> >
> > > im trying to iterate through 2 recordsets.
> > >
> > > the 2 recordsets contain data from 2 tables with a 1(rs1) to many (rs2)
> > > relationship.
> > >
> > > so the idea is for each loop of rs1 there should be serveral loops of rs2.
> > >
> > > but im stuck inside my loops and cant seem to find a way out.
> > >
> > > help please
> > >
> > > Set rs1 = CurrentDb.OpenRecordset(qry1)
> > > 'loop through transaction codes and check if items have been returned
> > > If Not (rs1.BOF And rs1.EOF) Then
> > > rs1.MoveFirst
> > > Do While Not rs1.EOF
> > > 'set closed to catch open record
> > > closed = True
> > > 'collect transcode from recordset and search transdata
> > > tcode = rs1.Fields("transcode")
> > > qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM Transdata
> > > WHERE Transdata.Transcode= " & tcode & ";"
> > > Set rs2 = CurrentDb.OpenRecordset(qry2)
> > > If Not (rs2.BOF And rs2.EOF) Then
> > > rs2.MoveFirst
> > > 'loop through transdata for each transaction code, set closed to
> > > true if open record found
> > > Do While Not rs2.EOF
> > > If IsNull(rs2.Fields("in")) Then
> > > closed = False
> > > rs2.MoveLast
> > > Else
> > > rs2.MoveNext
> > > End If
> > > Loop
> > > End If
> > > If closed = True Then
> > > rs1.Edit
> > > rs1.Fields("in date") = Date
> > > rs1.Update
> > > rs1.MoveNext
> > > End If
> > > Loop
> > > End If

Thanks very much guys

silly mistake on my part.

"Paolo" wrote:

> Thanx Dale,
> your explanation is more readable than mine but well, I wrote it this
> morning before my coffee...
>
> "Dale Fye" wrote:
>
> > What Paolo is trying to say is that your last few lines should look like:
> >
> > If closed = True Then
> > rs1.Edit
> > rs1.Fields("in date") = Date
> > rs1.Update
> > End If
> > 'the following line was move from inside the If/EndIf to outside it
> > rs1.MoveNext
> > Loop
> > End If
> >
> > ----
> > HTH
> > Dale
> >
> >
> >
> > "Paolo" wrote:
> >
> > > Hi Joseph Atie,
> > >
> > > you movenext the rs1 just when closed= true but if IsNull(rs2.Fields("in"))
> > > you put closed =false and you movelast the rs2 so you end this loop and so
> > > you don't movenext the rs1 and you enter again the first loop with the same
> > > value for tcode and so the rs2 will be the same so you'll never end this
> > > loop. You have to check the exit logic of your loop.
> > >
> > > HTH Paolo
> > >
> > > "Joseph Atie" wrote:
> > >
> > > > im trying to iterate through 2 recordsets.
> > > >
> > > > the 2 recordsets contain data from 2 tables with a 1(rs1) to many (rs2)
> > > > relationship.
> > > >
> > > > so the idea is for each loop of rs1 there should be serveral loops of rs2.
> > > >
> > > > but im stuck inside my loops and cant seem to find a way out.
> > > >
> > > > help please
> > > >
> > > > Set rs1 = CurrentDb.OpenRecordset(qry1)
> > > > 'loop through transaction codes and check if items have been returned
> > > > If Not (rs1.BOF And rs1.EOF) Then
> > > > rs1.MoveFirst
> > > > Do While Not rs1.EOF
> > > > 'set closed to catch open record
> > > > closed = True
> > > > 'collect transcode from recordset and search transdata
> > > > tcode = rs1.Fields("transcode")
> > > > qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM Transdata
> > > > WHERE Transdata.Transcode= " & tcode & ";"
> > > > Set rs2 = CurrentDb.OpenRecordset(qry2)
> > > > If Not (rs2.BOF And rs2.EOF) Then
> > > > rs2.MoveFirst
> > > > 'loop through transdata for each transaction code, set closed to
> > > > true if open record found
> > > > Do While Not rs2.EOF
> > > > If IsNull(rs2.Fields("in")) Then
> > > > closed = False
> > > > rs2.MoveLast
> > > > Else
> > > > rs2.MoveNext
> > > > End If
> > > > Loop
> > > > End If
> > > > If closed = True Then
> > > > rs1.Edit
> > > > rs1.Fields("in date") = Date
> > > > rs1.Update
> > > > rs1.MoveNext
> > > > End If
> > > > Loop
> > > > End If

Ha!

Like I've never forgotten to put a rs.movenext at the bottom of a loop.

Actually, If I remember correctly, I did this same thing just the other day.

Dale

"Joseph Atie" <(E-Mail Removed)> wrote in message
news:00F38260-25F1-4153-903C-(E-Mail Removed)...
> Thanks very much guys
>
> silly mistake on my part.
>
> "Paolo" wrote:
>
>> Thanx Dale,
>> your explanation is more readable than mine but well, I wrote it this
>> morning before my coffee...
>>
>> "Dale Fye" wrote:
>>
>> > What Paolo is trying to say is that your last few lines should look
>> > like:
>> >
>> > If closed = True Then
>> > rs1.Edit
>> > rs1.Fields("in date") = Date
>> > rs1.Update
>> > End If
>> > 'the following line was move from inside the If/EndIf to outside it
>> > rs1.MoveNext
>> > Loop
>> > End If
>> >
>> > ----
>> > HTH
>> > Dale
>> >
>> >
>> >
>> > "Paolo" wrote:
>> >
>> > > Hi Joseph Atie,
>> > >
>> > > you movenext the rs1 just when closed= true but if
>> > > IsNull(rs2.Fields("in"))
>> > > you put closed =false and you movelast the rs2 so you end this loop
>> > > and so
>> > > you don't movenext the rs1 and you enter again the first loop with
>> > > the same
>> > > value for tcode and so the rs2 will be the same so you'll never end
>> > > this
>> > > loop. You have to check the exit logic of your loop.
>> > >
>> > > HTH Paolo
>> > >
>> > > "Joseph Atie" wrote:
>> > >
>> > > > im trying to iterate through 2 recordsets.
>> > > >
>> > > > the 2 recordsets contain data from 2 tables with a 1(rs1) to many
>> > > > (rs2)
>> > > > relationship.
>> > > >
>> > > > so the idea is for each loop of rs1 there should be serveral loops
>> > > > of rs2.
>> > > >
>> > > > but im stuck inside my loops and cant seem to find a way out.
>> > > >
>> > > > help please
>> > > >
>> > > > Set rs1 = CurrentDb.OpenRecordset(qry1)
>> > > > 'loop through transaction codes and check if items have been
>> > > > returned
>> > > > If Not (rs1.BOF And rs1.EOF) Then
>> > > > rs1.MoveFirst
>> > > > Do While Not rs1.EOF
>> > > > 'set closed to catch open record
>> > > > closed = True
>> > > > 'collect transcode from recordset and search transdata
>> > > > tcode = rs1.Fields("transcode")
>> > > > qry2 = "SELECT Transdata.Transcode, Transdata.[In] FROM
>> > > > Transdata
>> > > > WHERE Transdata.Transcode= " & tcode & ";"
>> > > > Set rs2 = CurrentDb.OpenRecordset(qry2)
>> > > > If Not (rs2.BOF And rs2.EOF) Then
>> > > > rs2.MoveFirst
>> > > > 'loop through transdata for each transaction code, set
>> > > > closed to
>> > > > true if open record found
>> > > > Do While Not rs2.EOF
>> > > > If IsNull(rs2.Fields("in")) Then
>> > > > closed = False
>> > > > rs2.MoveLast
>> > > > Else
>> > > > rs2.MoveNext
>> > > > End If
>> > > > Loop
>> > > > End If
>> > > > If closed = True Then
>> > > > rs1.Edit
>> > > > rs1.Fields("in date") = Date
>> > > > rs1.Update
>> > > > rs1.MoveNext
>> > > > End If
>> > > > Loop
>> > > > End If