monkeybiz12345
asked on
Endless loop... please help me figure out why
Greetings, Experts!
I'm writing a function that checks for items received at zero cost. I have a SQL recordset to get the list of receipts to check. I want to loop through it and check the cost of each item received. If the item being checked has a nonzero cost, then move on and check the next item. If any item is found to have a zero cost, I want to immediately exit the loop and the function and return "false" to the calling program. If all items are found to have a nonzero cost, I want to return "true" to the calling program.
This is my function. I have one record in my recordset. It has a nonzero cost. I would have expected the loop to execute once, then exit and return true just before the 2nd time around because we should now be at the end of the recordset. Instead, it keeps looping and I can't figure out why.
Instead of the Do While Not... Loop construct, I have also tried Do... Loop While Not and Do .... Loop Until with no luck. What am i missing here?
Many thanks!
I'm writing a function that checks for items received at zero cost. I have a SQL recordset to get the list of receipts to check. I want to loop through it and check the cost of each item received. If the item being checked has a nonzero cost, then move on and check the next item. If any item is found to have a zero cost, I want to immediately exit the loop and the function and return "false" to the calling program. If all items are found to have a nonzero cost, I want to return "true" to the calling program.
This is my function. I have one record in my recordset. It has a nonzero cost. I would have expected the loop to execute once, then exit and return true just before the 2nd time around because we should now be at the end of the recordset. Instead, it keeps looping and I can't figure out why.
Instead of the Do While Not... Loop construct, I have also tried Do... Loop While Not and Do .... Loop Until with no luck. What am i missing here?
Many thanks!
Function IsItemCostNonZero()
dim cn, rstItems, strConnectionString, strSQLInstanceName, strDatabaseName, strSQL
set cn = createObject("ADODB.Connection")
strSQLInstanceName="XX-SQ01"
strDatabaseName="YYYY"
strConnectionString = "Provider=SQLOLEDB;Data Source=" & strSQLInstanceName & ";Persist Security Info=False;Initial Catalog=" & strDatabaseName & ";Integrated Security=SSPI"
cn.Open strConnectionString
' get list of items to check
strSQL = "SELECT Job, StockCode " & _
"FROM dbo.ItemReceipts " & _
"WHERE Job = '" & Form.Job & "' And RecType = 'I' " & _
"GROUP BY Job, StockCode"
Set rstItems = CreateObject("ADODB.Recordset")
rstItems.Open strSQL,cn
If rstItems.EOF Then
msgbox "No records in the recordset."
Exit Function
End If
dim ItemOK, strWH, strStockCode
strWH = "XXX"
strStockCode = rstItems.Fields("StockCode").Value
Do While NOT rstItems.EOF
' exit loop if Item is found to have 0 cost
If GetUnitCost(strWH, strStockCode) = 0 Then
ItemOK = false
Exit Do
End If
ItemOK=true
Loop
' Close & release the recordset
rstItems.Close
set rstItems = nothing
IsItemCostNonZero = ItemOK
End Function
You need a MoveNext someplace.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, I do! I knew it was something I should've been able to spot immediately and just wasn't seeing. Thank you!
You're welcome and I'm glad I was able to help.
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014