stuck in a loop

J

Joseph Atie

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
 
P

Paolo

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
 
D

Dale Fye

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 said:
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 said:
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
Click to expand...
Click to expand...
 
P

Paolo

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

Dale Fye said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joseph Atie

Thanks very much guys

silly mistake on my part.

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

Dale Fye said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

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 said:
Thanks very much guys

silly mistake on my part.

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

Dale Fye said:
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



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error in VBA Code 1
"Object doesn't support this property or method" Error on Tree Con 0
Data Type Conversion error 5
Split not working correctly 2
next without for error - stumped 3
Empty Recordset 3
SQL Select in VBA to Open Recordset 2
update previous record 1

Top