update statement having no effect during loop run

[cdck]

I have a routine which builds a bill of materials one level at a time (part A is made of parts B.1, b.2, and b.3; B.1 is made of c.1, c.2, c.3, etc). Once the BOM is built, I need to go back to sub-parts and multiply their quantity by the quantity of the parent part, so that if it takes two of B.1 to make an A, and it takes three of C.1 to make a B.1, the total qty of C.1 in one A is 6.

I have written a query which works outside of the module, and updates every sub-part by multiplying its qty by the parent qty. This is the query that works on that level:
[tt]
UPDATE (tblBomTemp INNER JOIN qryCompQtyMeanPar ON (tblBomTemp.fcomponent = qryCompQtyMeanPar.comp) AND (tblBomTemp.fparent = qryCompQtyMeanPar.Par)) INNER JOIN qryCompParentQtynSrc ON (qryCompQtyMeanPar.ParMark = qryCompParentQtynSrc.ParMark) AND (qryCompQtyMeanPar.Par = qryCompParentQtynSrc.Par) SET tblBomTemp.fqty = [CompQty]*[ParQty]
WHERE (((qryCompParentQtynSrc.levelNum)>0));
[/tt]
At the end, you see that it is looking at "levelNum"; in this case it is affecting everything in the table where the levelNum is not 0. I need to work through the levels one at a time, from lowest to highest, and do the multiplication to each level as it comes to it. So I have written this module:
[tt]
Public Function CostFix()

On Error Resume Next
Dim lev, lev2, SQLup

'first update the description of all parts that will be affected.
DoCmd.RunSQL "UPDATE tblBomTemp SET fdescript = Trim([fdescript]) & ' (' & [fqty] & ' ' & [fmeasure] & ')' WHERE levelNum > 1;"

'note and record the highest level on the BOM
lev = DLookup("[Level]", "qryBOMMaxLev")

MsgBox (lev)

'set the updates to start with level 2
lev2 = 2

'set the loop to stop when it finishes the highest level on the BOM
Do Until lev2 = lev + 1

MsgBox (lev2 & " of " & lev)

'update qtys on parts where level = lev2
SQLup = "UPDATE (tblBomTemp INNER JOIN qryCompQtyMeanPar ON (tblBomTemp.fparent = qryCompQtyMeanPar.Par) AND (tblBomTemp.fcomponent = qryCompQtyMeanPar.comp)) INNER JOIN qryCompParentQtynSrc ON (qryCompQtyMeanPar.Par = qryCompParentQtynSrc.Par) AND (qryCompQtyMeanPar.ParMark = qryCompParentQtynSrc.ParMark) SET tblBomTemp.fqty = [CompQty]*[ParQty] WHERE (((qryCompParentQtynSrc.levelNum)=" & lev2 & "));"
MsgBox (SQLup)
DoCmd.RunSQL (SQLup)

lev2 = lev2 + 1
Loop
End Function
[/tt]
As you can see, I currently have message boxes popping up to show me the values of lev, lev2, and SQLup at each step, and all of these values look correct. However, when all is said and done, the quantities are unaffected.

I have verified that the field "LevelNum" is a number field.

Can anyone see what's wrong in the code?

Cheryl dc Kern

 

[cdck]

There was nothing wrong with the code, but with the level it started processing at. Once set to level 1, my test unit was affected; the level was the level of the parent, not of the part being changed.

My apologies to anyone who looked this over.

Cheryl dc Kern