Either EOF Or BOF is true, Or the current record has been deleted ----- Issue in VB

See more:

VB6

Dear All,

I am getting the error:"Either EOF Or BOF is true, Or the current record has been deleted" when I click the process button which has the following code:

Cnn.BeginTrans
Cnn.Execute ("Delete from ClaimAndPromoMergeData")
Cnn.Execute ("Proc_MergeClaimAndPromo")
Cnn.Execute ("Delete from ArrowOutput")
SQL = "Insert Into ArrowOutput SELECT DISTINCT STM.[Buyer - Company ID], STM.[Buyer - Company Name], STM.[Buyer - Location ID]," & _
"STM.[Buyer - Location Post City], STM.[Buyer - Location Post State], STM.[Fiscal Quarter], STM.[Fiscal Month], STM.[Product Line ID]," & _
"STM.[Product Number (SKU)], STM.[Product Type], STM.[Product Class], STM.[RAS Model], STM.[RAS Family], STM.[RAS Product], STM.GBU, STM.Segment," & _
"STM.[Seller - Company ID], STM.[Seller - Company Name], STM.[Trans Date], STM.[Trans Doc ID], STM.[Trans Doc Type], STM.[Buyer - Location Channel Segment]," & _
"STM.[Buyer - Channel Membership], STM.[Buyer - Location Name], STM.[Submitted Txn Unit ST@LC(IN&SP Only)], STM.[Relationship Sell Thru Qty]," & _
"STM.[Relationship Sell Thru Option Qty], STM.[Relationship Sell Thru @ USD], STM.[Eclipse ID], '' AS [Eligible-Not Eligible], '' AS [Eligible Qty]," & _
"AM.[Claim Line Item ID],AM.[Claim Line Item Status],DM.[Denial_comment] As [Claim Denial Error Comment1]" & _
"FROM denial_master AS DM,SellThruMaster AS STM Inner Join ClaimAndPromoMergeData AS AM On (((STM.[Product Number (SKU)])=[AM].[Product Number (SKU)]) AND " & _
"((STM.[Seller - Company ID])=[AM].[Seller - Company ID]) AND ((STM.[Trans Date])=[AM].[Trans Date]) AND ((STM.[Trans Doc ID])=[AM].[Trans Doc ID])  AND ((AM.[Claim Line Item Status])='Eligible')) Where DM.[Denial_code]=AM.[Claim Denial Error Code 1]"
Cnn.Execute (SQL)

SQL1 = "Insert Into ArrowOutput Select Distinct STM.[Buyer - Company ID],STM.[Buyer - Company Name],STM.[Buyer - Location ID]," & _
"STM.[Buyer - Location Post City],STM.[Buyer - Location Post State],STM.[Fiscal Quarter],STM.[Fiscal Month],STM.[Product Line ID]," & _
"STM.[Product Number (SKU)],STM.[Product Type],STM.[Product Class],STM.[RAS Model],STM.[RAS Family],STM.[RAS Product]," & _
"STM.[GBU],STM.[Segment],STM.[Seller - Company ID],STM.[Seller - Company Name],STM.[Trans Date],STM.[Trans Doc ID]," & _
"STM.[Trans Doc Type],STM.[Buyer - Location Channel Segment],STM.[Buyer - Channel Membership],STM.[Buyer - Location Name]," & _
"STM.[Submitted Txn Unit ST@LC(IN&SP Only)],STM.[Relationship Sell Thru Qty],STM.[Relationship Sell Thru Option Qty]," & _
"STM.[Relationship Sell Thru @ USD],STM.[Eclipse ID],'' As [Eligible-Not Eligible],'' As [Eligible Qty]," & _
"AM.[Claim Line Item ID],AM.[Claim Line Item Status],DM.[Denial_comment] As [Claim Denial Error Comment1]" & _
"FROM denial_master AS DM,SellThruMaster AS STM Inner Join ClaimAndPromoMergeData AS AM On (((STM.[Product Number (SKU)])=[AM].[Product Number (SKU)]) AND " & _
"((STM.[Seller - Company ID])=[AM].[Seller - Company ID]) AND ((STM.[Trans Date])=[AM].[Trans Date]) AND ((STM.[Trans Doc ID])=[AM].[Trans Doc ID])  AND ((AM.[Claim Line Item Status])='Non-Eligible')) Where DM.[Denial_code]=AM.[Claim Denial Error Code 1]"
Cnn.Execute (SQL1)

'Not Eligible - Other
'Update Eligible Status and qty
Set Rec = New ADODB.Recordset
Rec.Open "Select * from ArrowOutput", Cnn, adOpenDynamic, adLockOptimistic
Do Until Rec.EOF
SQL = "Select IIF(Sum(IIf([Claim Line Item Status]='Eligible',1,0))>0,'Eligible','Not Eligible - Others') As Status," & _
"Sum(IIf([Claim Line Item Status]='Eligible',1,0)) As EligibleQty " & _
"from ClaimAndPromoMergeData Where [Product Number (SKU)]='" & Rec.Fields("Product Number (SKU)") & "' And " & _
"[Seller - Company ID]='" & Rec.Fields("Seller - Company ID") & "' And [Trans Date]=#" & Format(Rec.Fields("Trans Date"), "dd-mmm-yyyy") & _
"# And [Trans Doc ID] ='" & Rec.Fields("Trans Doc ID") & "' And [Claim Line Item Status]='Eligible'"
Rec1.Open SQL, Cnn
If Not Rec1.EOF Then
    Rec.Fields("Eligible-Not Eligible") = Rec1.Fields("Status") & ""
    Rec.Fields("Eligible Qty") = Rec1.Fields("EligibleQty") & ""
    'Rec.Fields("NonEligibleQty") = Rec1.Fields("NonEligibleQty") & ""
End If
Rec1.Close
Rec.MoveNext
SQL1 = "Select IIF(Sum(IIf([Claim Line Item Status]='Non-Eligible',1,0))>0,'Not Eligible - Others','Eligible') As Status," & _
"Sum(IIf([Claim Line Item Status]='Non-Eligible',1,0)) As EligibleQty " & _
"from ClaimAndPromoMergeData Where [Product Number (SKU)]='" & Rec.Fields("Product Number (SKU)") & "' And " & _
"[Seller - Company ID]='" & Rec.Fields("Seller - Company ID") & "' And [Trans Date]=#" & Format(Rec.Fields("Trans Date"), "dd-mmm-yyyy") & _
"# And [Trans Doc ID] ='" & Rec.Fields("Trans Doc ID") & "' And [Claim Line Item Status]='Non-Eligible'"
Rec1.Open SQL1, Cnn
If Not Rec1.EOF Then
    Rec.Fields("Eligible-Not Eligible") = Rec1.Fields("Status") & ""
    'Rec.Fields("Eligible Qty") = Rec1.Fields("EligibleQty") & ""
    Rec.Fields("Eligible Qty") = Rec1.Fields("EligibleQty") & ""
End If
Rec1.Close
Rec.MoveNext
Loop
Rec.UpdateBatch
Cnn.CommitTrans
Rec.Close

Here I am using two different queries SQL and SQL1. I have a doubt in If statement after the conn.execute(SQL1). I have pasted the lengthy code and sorry for this. But I have to paste this entire code for undrestanding the issue.
Please any one suggest me on this issue.

Regards,