Compiler ignoring the with/Do in a loop?

[tonygg]

Hi. I was hoping for a little help with a compiling problem. In my sub I have a with a record set do and then next loop. The compiler thinks I have the loop statement but not the Do (which is clearly there!). I have looked to see if the problem is with the if/end if statements inside the loop but they all seem fine. I am sure it is a basic mistake but I just cant see it. I would be very grateful if someone could take a look. Many thanks. Tony

 
' Import Statement Spreadsheet and clean data


Dim SQLOrderString As String
Dim SQLComponentString As String
Dim strUpdate As String
Dim rsOrders As DAO.Recordset
Dim rsComponents As DAO.Recordset
Dim intPos As Integer


Dim BatchesAdded As Integer
Dim TxnsRecordsAdded As Integer
Dim TxnsRecordsNotAdded As Integer


Dim InsertRiskValue As String
Dim InsertBatchValue As Integer


Dim LastbatchNumUsed As Integer


Dim InPaymentAmount As Double
Dim InMerchantCommissionAmount As Double
Dim InTransferAmount As Double


Dim Filepath As String
Dim user As String


Set db = CurrentDb


BatchesAdded = 0
TxnsRecordsAdded = 0
TxnsRecordsNotAdded = 0
LastbatchNumUsed = 0


 
' Get CAPTURED or SENT_FOR_REFUND or AUTRHORISED transactions from GetStatement Import


SQLOrderString = "SELECT TONYGetStatement.Parameters As TransactionID, TONYGetStatement.F2 AS AuthorisedDate, TONYGetStatement.F3 AS CapturedDate, TONYGetStatement.F4 AS EventType, TONYGetStatement.F5 AS Payccy, TONYGetStatement.F6 AS PayAmt, TONYGetStatement.F7 AS Risk, TONYGetStatement.F9 AS CartID, TONYGetStatement.F14 AS InProcessAmt, TONYGetStatement.F15 AS CapturedAmt, TONYGetStatement.F16 AS EventDate, TONYGetStatement.F17 AS Shopperccy, TONYGetStatement.F18 AS PayMethod,  TONYGetStatement.F19 AS 3DSecureResult" & _
" FROM TONYGetStatement" & _
" WHERE (((IsNull([TONYGetStatement].[Parameters])) = False) And ((Val([TONYGetStatement].[Parameters])) <> 0) AND ( ([TONYGetStatement].[F4] = 'CAPTURED') OR ([TONYGetStatement].[F4] = 'SENT_FOR_REFUND') OR ([TONYGetStatement].[F4] = 'AUTHORISED')   ) )" & _
" ORDER BY ([TONYGetStatement].F16);"


Set rsOrders = Nothing


Set rsOrders = CurrentDb.OpenRecordset(SQLOrderString)


If Not (rsOrders.EOF) Then


 ' Loop through all the get statement records


 With rsOrders
  Do Until .EOF
  
   ' Look to see if the tranaction record already exists in TONYWorldPayTransactions


     SQLComponentString = "SELECT TONYWorldPayTransactions.* " & _
     " FROM TONYWorldPayTransactions" & _
     " WHERE (TONYWorldPayTransactions.[Transaction ID] = " & Chr$(39) & rsOrders![TransactionID] & Chr$(39) & ") AND ( TONYWorldPayTransactions.[Cart Id] = " & Chr$(39) & rsOrders![CartID] & Chr$(39) & ") AND ( TONYWorldPayTransactions.[Captured Date] = Cdate(" & Chr$(39) & rsOrders![CapturedDate] & Chr$(39) & " ) ) AND ( TONYWorldPayTransactions.[Authorised Date] = Cdate(" & Chr$(39) & rsOrders![AuthorisedDate] & Chr$(39) & " ) ) AND ( TONYWorldPayTransactions.[Event Type] = " & Chr$(39) & rsOrders![EventType] & Chr$(39) & " );"
     
     Set rsComponents = Nothing


     Set rsComponents = CurrentDb.OpenRecordset(SQLComponentString)


     If (rsComponents.EOF) Then
     
        rsComponents.Close
        Set rsComponents = Nothing
     
       ' Transaction record doesnt exists so add it to TONYWorldPayTransactions
       
        If (rsOrders![EventType] = "CAPTURED") Or (rsOrders![EventType] = "SENT_FOR_REFUND") Then
                                  
           ' Find batch number in the Barclay Batch Table
     
           SQLComponentString = "SELECT TONYWorldPayBatchtoBarclay.[Batch ID], TONYWorldPayBatchtoBarclay.[Period Begin Date], TONYWorldPayBatchtoBarclay.[Period End Date] " & _
           " FROM TONYWorldPayBatchtoBarclay " & _
           " WHERE ( CDATE(TONYWorldPayBatchtoBarclay.[Period End Date]) >= CDate(" & Chr$(39) & rsOrders![EventDate] & Chr$(39) & ") AND CDATE(TONYWorldPayBatchtoBarclay.[Period Begin Date]) < CDate(" & Chr$(39) & rsOrders![EventDate] & Chr$(39) & ") )" & _
           " ORDER BY TONYWorldPayBatchtoBarclay.[Batch ID];"


           Set rsComponents = CurrentDb.OpenRecordset(SQLComponentString)


           If Not (rsComponents.EOF) Then
           
             ' Set the batch number to import with the transaction
             
               InsertBatchValue = rsComponents![Batch ID]
               
               ' Check that batches are sequential and if not then warn that a batch has no transactions
               
               If InsertBatchValue > (LastbatchNumUsed + 1) Then
               
                  If MsgBox("WARNING: Batches from batch number : " & (LastbatchNumUsed + 1) & " to batch number : " & (InsertBatchValue - 1) & " have no transactions. Is this OK to proceed?", vbInformation + vbYesNo) = vbNo Then
                  
                  rsOrders.Close
                  Set rsOrders = Nothing
  
                  rsComponents.Close
                  Set rsComponents = Nothing
                  
                  Me.LatestTxnDateUpload = DMax("[Captured Date]", "TONYWorldPayTransactions", "[Event Type] = 'SETTLED'")
        
                  Exit Sub
        
               Else
               
                 LastbatchNumUsed = InsertBatchValue
                  
               End If
               
           Else
           
              MsgBox "Transactions found with dates : " & rsOrders![EventDate] & " which is past the last batch number : " & LastbatchNumUsed & " so remaining transactions will be ignored"
              
              Me.LatestTxnDateUpload = DMax("[Captured Date]", "TONYWorldPayTransactions", "[Event Type] = 'SETTLED'")
              
              rsOrders.Close
              Set rsOrders = Nothing
  
              rsComponents.Close
              Set rsComponents = Nothing
        
              Exit Sub
        
           End If
 
 
        Else ' It is an AUTHORISED record
    
          InsertBatchValue = 0
       
        End If
             
             
        ' Convert fields with Null values
        
        If IsNull(rsOrders![Risk]) Then
        
           InsertRiskValue = " "
          
        Else
          
           InsertRiskValue = rsOrders![Risk]
          
        End If
       
       
       ' Decide how to populate record and insert in TONYWorldPayTransactions
       
       If (rsOrders![EventType] = "AUTHORISED") Then
    
           strUpdate = ""


           strUpdate = "INSERT INTO TONYWorldPayTransactions " & vbCrLf & _
            " ([Transaction ID], [Authorised Date], [Captured Date], [Event Type], [Payment Currency], [Payment Amount], [Risk], [Batch Id], [Cart Id], BatchReconciled, WPBarclayFlag, CountryofCard, CardType, 3DResult )" & vbCrLf & _
            " VALUES (" & Chr$(39) & rsOrders![TransactionID] & Chr$(39) & " ," & Chr$(39) & rsOrders![AuthorisedDate] & Chr$(39) & " ," & Chr$(39) & rsOrders![CapturedDate] & Chr$(39) & vbCrLf & _
            " , AUTHORISED , 'GBP', " & CCur(rsOrders![PayAmt]) & " , " & Chr$(39) & InsertRiskValue & Chr$(39) & " , " & InsertBatchValue & vbCrLf & _
            " , " & Chr$(39) & rsOrders![CartID] & Chr$(39) & " , False, 'B' , " & Chr$(39) & rsOrders![Shopperccy] & Chr$(39) & " , " & Chr$(39) & rsOrders![PayMethod] & Chr$(39) & " , " & Chr$(39) & rsOrders![3DSecureResult] & Chr$(39) & " );"


       Else
        
          If (rsOrders![EventType] = "SENT_FOR_REFUND") Then
           
              strUpdate = ""
           
              strUpdate = "INSERT INTO TONYWorldPayTransactions " & vbCrLf & _
              " ([Transaction ID], [Authorised Date], [Captured Date], [Event Type], [Payment Currency], [Payment Amount], [Risk], [Batch Id], [Cart Id], [Merchant Commission Currency], [Merchant Commission Amount], [Transfer Currency], [Transfer Amount], BatchReconciled, WPBarclayFlag, CountryofCard, CardType, 3DResult )" & vbCrLf & _
              " VALUES (" & Chr$(39) & rsOrders![TransactionID] & Chr$(39) & " ," & Chr$(39) & rsOrders![EventDate] & Chr$(39) & " ," & Chr$(39) & rsOrders![EventDate] & Chr$(39) & vbCrLf & _
              " , REFUNDED , 'GBP', " & CCur(rsOrders![PayAmt]) & " , " & Chr$(39) & InsertRiskValue & Chr$(39) & " , " & InsertBatchValue & vbCrLf & _
              " , " & Chr$(39) & rsOrders![CartID] & Chr$(39) & " , 'GBP', 0 , 'GBP' , " & CCur(rsOrders![CapturedAmt]) & vbCrLf & _
              " , False, 'B' , " & Chr$(39) & rsOrders![Shopperccy] & Chr$(39) & " , " & Chr$(39) & rsOrders![PayMethod] & Chr$(39) & " , " & Chr$(39) & rsOrders![3DSecureResult] & Chr$(39) & " );"
           
          Else
              
              ' Must be CAPTURED Transaction
        
              strUpdate = ""
           
              strUpdate = "INSERT INTO TONYWorldPayTransactions " & vbCrLf & _
              " ([Transaction ID], [Authorised Date], [Captured Date], [Event Type], [Payment Currency], [Payment Amount], [Risk], [Batch Id], [Cart Id], [Merchant Commission Currency], [Merchant Commission Amount], [Transfer Currency], [Transfer Amount], BatchReconciled, WPBarclayFlag, CountryofCard, CardType, 3DResult )" & vbCrLf & _
              " VALUES (" & Chr$(39) & rsOrders![TransactionID] & Chr$(39) & " ," & Chr$(39) & rsOrders![AuthorisedDate] & Chr$(39) & " ," & Chr$(39) & rsOrders![CapturedDate] & Chr$(39) & vbCrLf & _
              " , SETTLED , 'GBP', " & CCur(rsOrders![PayAmt]) & " , " & Chr$(39) & InsertRiskValue & Chr$(39) & " , " & InsertBatchValue & vbCrLf & _
              " , " & Chr$(39) & rsOrders![CartID] & Chr$(39) & " , 'GBP', 0 , 'GBP' , " & CCur(rsOrders![CapturedAmt]) & vbCrLf & _
              " , False, 'B' , " & Chr$(39) & rsOrders![Shopperccy] & Chr$(39) & " , " & Chr$(39) & rsOrders![PayMethod] & Chr$(39) & " , " & Chr$(39) & rsOrders![3DSecureResult] & Chr$(39) & " );"


          End If
        
       End If



       db.Execute strUpdate, dbFailOnError
       
       
       TxnsRecordsAdded = TxnsRecordsAdded + 1
     
     Else
   
       ' Ignore the record
       
       TxnsRecordsNotAdded = TxnsRecordsNotAdded + 1
       
       rsComponents.Close
       Set rsComponents = Nothing
       
     End If
       
   .MoveNext
    Loop
 End With
 
 ' highlight number of transactions added and how many were ignored as already exist
 
 MsgBox TxnsRecordsAdded & " Transaction records Added and " & TxnsRecordsNotAdded & " Transaction records Ignored as they are already imported"
 
 rsOrders.Close
 Set rsOrders = Nothing



Else


  ' No transactions found in the file


  MsgBox "No Captured/Settled Trades found to process in the import!"
  
  rsOrders.Close
  Set rsOrders = Nothing
  


End If


Me.LatestTxnDateUpload = DMax("[Captured Date]", "TONYWorldPayTransactions", "[Event Type] = 'SETTLED'")



End Sub

[tonygg]

found the issue. It was those pesky end ifs. sorry if it has caused anyone any work in debugging. thanks