Looping error on last row of table :banghead:

[glasgowlad1999]

Hi There,

I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.

If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.

Can you help...

Function RunLWSPA()
 Dim cnn As ADODB.Connection
   Dim rstUplo As New ADODB.Recordset
   Dim rstDiag As New ADODB.Recordset
   Dim rstTemp As New ADODB.Recordset
   Dim SQL As String
   Dim strDiag
   
   Set cnn = CurrentProject.Connection
   
   ' Clear temporary table
   SQL = "Delete * from [LWorkSheetPA]"
   cnn.Execute SQL
   
   ' Open temporary table as a recordset
   rstTemp.Open "LWorkSheetPA", cnn, adOpenForwardOnly, adLockPessimistic
   
   ' Loop through Upload table
   rstUplo.Open "tblProcedure", cnn, adOpenForwardOnly, adLockReadOnly
   Do While Not rstUplo.EOF
      ' Add a new record in temporary table
      ' and copy Item and Description to it
      rstTemp.AddNew
      rstTemp![Claim_Numbers] = rstUplo![Claim_Number]
      rstTemp![FacIDs] = rstUplo![FacID]
      rstTemp.Update
      
     ' Concatenate Diagnosis_Code_1 in a string
    SQL = "Select Format([Paid_Amount],'Currency') as [Paid_Amount] from [tblProcedure]" & _
          " where [Claim_Number]='" & rstUplo![Claim_Number] & "'"
      rstDiag.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
      strDiag = ""
      Do While Not rstDiag.EOF
         strDiag = strDiag & "   " & rstDiag![Paid_Amount]
         rstDiag.MoveNext
      Loop
      ' Remove leading comma and space from concatenated string
      ' and update temporary table with the string
      strDiag = Mid(strDiag, 3)
      rstTemp![Paid_Amount] = strDiag
      rstDiag.Close
      rstUplo.MoveNext
   Loop


   ' Clean up
   Set cnn = Nothing
   Set rstUplo = Nothing
   Set rstDiag = Nothing
   Set rstTemp = Nothing
   
   
End Function

 

[vbaInet]

You don't need to keep opening and closing the recordset.

Have you set a watch on the strDiag when it gets to the last record?
Have you Debug.Print strDiag to see what value it is when it gets to the last record?

By the way interesting name for table name, "banghead"

Looping error on last row of table :banghead:

 

[pbaldy]

You've already updated the recordset at the point when you set that value. Try moving the update to after that line.

 

[glasgowlad1999]

vbaInet

How do you do that? I am really new and managed to make different scripts work to get this far.

 

[glasgowlad1999]

Thank you

Moving the update to inside the loop fixed the issue

Loop
      ' Remove leading comma and space from concatenated string
      ' and update temporary table with the string
      strDiag = Mid(strDiag, 2)
      rstTemp![Charge_Amount] = strDiag
      rstDiag.Close
      rstUplo.MoveNext
      rstTemp.Update
   Loop

 

[pbaldy]

Happy to help.

 

[glasgowlad1999]

Wish I could find the same great help with an excel issue I am having, that I posted to excelforum...