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!




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

Open in new window

monkeybiz12345Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You need a MoveNext someplace.
0
Martin LissOlder than dirtCommented:
I added line 35.
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
		rstItems.MoveNext
		ItemOK=true
			
   Loop 

   ' Close & release the recordset
   rstItems.Close
   set rstItems = nothing

IsItemCostNonZero = ItemOK

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monkeybiz12345Author Commented:
So, I do!  I knew it was something I should've been able to spot immediately and just wasn't seeing.  Thank you!
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.