glasgowlad1999
Registered User.
- Local time
- Today, 08:07
- Joined
- Jun 17, 2013
- Messages
- 27
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...
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...
Code:
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