Loop runs once and then stops. 1

[cdck]

I'm working on a loop until statement (see below). My problem is that with the record this loop is working from, it should run 3 times, but it is only running one time and then stopping. Can anyone see what I did wrong?
[tt]
AloopStop = "N"

Do Until AloopStop = "Y"

If levelNum = 25 Then AloopStop = "Y"

If DCount("fcomponent", "inboms", "fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = """ & levelNum & """)") = 0 Then AloopStop = "Y"

DoCmd.RunSQL "INSERT INTO tblBomTemp ( levelNum, MarkNum, fcomponent, fcompudrev, fitem, fparent, fcparudrev, fqty, fbommemo, cfacilityid, valEAinmast, valEAinvend, fdescript, fsource, fcpurchase )SELECT " & levelNum + 1 & " AS [Level], [tblBomTemp].[MarkNum] & '.' & Right([dbo_inboms].[fitem],3) AS MarkNumn, dbo_inboms.fcomponent, dbo_inboms.fcompudrev, Right([dbo_inboms].[fitem],3) AS Item, dbo_inboms.fparent, dbo_inboms.fcparudrev, dbo_inboms.fqty, dbo_inboms.fbommemo, dbo_inboms.cfacilityid, dbo_inmast.favgcost, qryLastPO.fvlastpc, dbo_inmast.fdescript, dbo_inmast.fsource, dbo_inmast.fcpurchase FROM ((dbo_inboms LEFT JOIN dbo_inmast ON dbo_inboms.fcomponent = dbo_inmast.fpartno) LEFT JOIN qryLastPO ON dbo_inboms.fcomponent = qryLastPO.fpartno) INNER JOIN tblBomTemp ON dbo_inboms.fparent = tblBomTemp.fcomponent WHERE (((tblBomTemp.levelNum)=" & levelNum & "));"

levelNum = levelNum + 1

Loop[/tt]

To clarify what I tried to write the loop to do, this loop runs after a set of records is entered into tblBomTemp. The queries in the loop look at tblBomTemp and compare it to the original data. For each item that has been added to tblBomTemp which has a "levelNum" that matches the current levelNum variable, it finds all records in the original data that list that component as a parent. These records are then inserted into tblBomTemp with their levelNum set to one higher. The levelNum variable is then raised to the next level, and it loops.

At the beginning of the loop, It checks if too many levels have passed and if there are no records to pull matching the above requirements. In either case, it will stop the loop. It appears, however, that it runs through the loop code one time, but then does not move on to pulling and inserting the level 3 and then level 4 data - which I have confirmed exists in the original tables.

Cheryl dc Kern

 

[dinger2121]

did you step through the code. Usually if you do this, you can see exactly if/where it is erroring out. This may also help you to determine why it is exiting the loop early as you can look at your variables at various points.

 

[cdck]

I just got done taking out bits of code to see if I could get it to work without one or another, and found that it is the If then with the Dlookup that is the problem. I tried add an Else to the If Then statement, but it had no effect. It still stops the loop the second time that it gets to the if then.

Without the If then including the Dloopup, the loop functions correctly, though it takes a long time because it has to iterate out until levelNum becomes 25 before it can stop the loop.

Can anyone see what is wrong with the Dlookup section/logic? It is especially confusing, because it does work the first time that the code reaches it, and only fails me when it loops back to it.



Cheryl dc Kern

 

[rheitzman]

You might consider using EXIT FOR

FOR lvl = 1 to 25
IF ... THEN
EXIT FOR
END IF
....
NEXT lvl

Using END IF is also helpful if you are stepping thorough the code so you can stop and look at things in the middle of the IF evaluation.

 

[TheAceMan1]

How are ya cdck . . .

take note . . . in:

[blue]If [purple][b]levelNum = 25[/b][/purple] Then AloopStop = "Y"[/blue]

You treat LevelNum as [purple]numeric![/purple] . . . But in:

[blue]If DCount("fcomponent", "inboms", "fparent IN (SELECT fcomponent FROM tblBomTemp WHERE [purple][b]levelNum = """ & levelNum & """[/b][/purple])") = 0 Then AloopStop = "Y"[/blue]

You treat LevelNum as a [purple]string![/purple] . . . Which is it? . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[cdck]

TheAceMan1:
levelNum is numeric, thanks. I added the '' to correct that, giving me:

[tt]If DCount("fcomponent", "inboms", "fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = '""" & levelNum & """')") = 0 Then AloopStop = "Y"[/tt]

but it still stops the loop.

rheitzman:

The problem is that I don't want it to run 25 times unless there is data to pull 25 times; if there isn't, I want it to end right away when it runs out of useful data.

I'm not sure what "stepping through the code" means - I assumed that it meant finding the point where the code fails. Have I done that by pinpointing the issue in the Dcount code above by determining that without that code, the loop runs fine - just 22 unnecessary and lengthy times?

I do appreciate everyone's help with this.

Cheryl dc Kern

 

[dinger2121]

If you enter your visual basic editor you can click in the margin on the left of your code to put a break point (shows up as a red dot and highlights that line of code). You then use F8 to step through the code. As you are stepping through the code, you can hover over your variables to see what values they are holding at that point. You can also right-click on a variable and choose add watch to have it show up in the watches window and this will display the variable value as you step through.

 

[PHV]

levelNum is numeric
If DCount("fcomponent", "inboms", "fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum=" & levelNum & ")") = 0 Then AloopStop = "Y"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdck]

Thanks, PHV - I did actually try that punctuation before, but I thought I'd copy it from you and try again to see if I overlooked something else. It still runs through the loop one time and stops.

Thanks to dinger2121's instructions, I stepped through the code.

The very first time that it runs the Dcount If statement, it sees Dcount as 0 and sets the AloopStop variable to Y. It doesn't end the loop until it has passed through the rest of the loop code, however - it ends when it returns to the Do Until (which makes sense, though I'd like it to exit the loop immediately - I can live with that).

What doesn't make sense is that it should be seeing the Dcount as greater than 0 until levelNum becomes 4 in my test case, because there are 4 levels of record that are pulled when I comment out the Dcount If statement.

Is there a better way for me to perform this check and avoid using the Dcount function?

Cheryl dc Kern