Search code examples
excelvbaloopsnested-loops

Keeps jumping back to start of Sub as soon as it completes first iteration in the nested loop


Very new to VBA coding. I inserted a Text Box (Active Control X) in my worksheet. Wrote a code to import data from MS Access database and save that data to an array. Later I am trying to print that array in the text box for user to see. but everytime my code enters the nested part of For loop, the running iteration of sub jumps back to the start of the code. Code than runs for multiple times make multiple SQL queries and excel crashes. I am not sure why code is jumping back to start of the sub?

Private Sub TextBox1_Change()

    Dim sQuery As String
    Dim ReturnData() As Variant
    'Clear existing data in statuses area
    Dim rngClearArea As Range
    Dim wsFleetio As Worksheet
        
    Set wsFleetio = ThisWorkbook.Worksheets("Test")
    Dim Farm As String
    
    Farm = wsFleetio.Range("B1").Value
    'Set rngClearArea = FindTag(wsFleetio, "$Vehicle Status", 2, 0).Resize(1000, 4)
    'rngClearArea.ClearContents
    
    'Build query
    sQuery = "SELECT [KillDate], [FarmName], [LoadType] FROM Loads WHERE ([FarmName] = '" & Farm & "' AND [KillDate] >= DateAdd('yyyy', -1, Date()))"
    
    ReturnData = GetMerlinData(sQuery)
    Dim leng As Integer
    leng = UBound(ReturnData, 2)
    
    Dim FarmData(500, 2) As Variant
    Dim m As Integer
    
    For m = 0 To UBound(ReturnData, 2)
        FarmData(m, 0) = ReturnData(0, m)
        FarmData(m, 1) = ReturnData(1, m)
        FarmData(m, 2) = ReturnData(2, m)
    Next
    
    Dim i As Long, j As Long
    
    For i = 0 To UBound(ReturnData, 2)
        For j = 0 To 2
            
            TextBox1.Text = TextBox1.Text & FarmData(i, j) & "---"
        
        Next j
        TextBox1.Text = TextBox1.Text & vbCrLf
    Next i

End Sub

After running the first iteration of j, code jumps back to start of the code. I want it to run normally but not sure what the error is


Solution

  • Add a second TextBox, TextBox2 and use it in the loop: TextBox2.Text = TextBox2.Text & ...