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

[Pardeep Singh]

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

 

[DanteAmor]

Hi and welcome to MrExcel!

Every time you modify the textbox in these lines, the TextBox1_Change event is fired again, so it goes into an endless loop.

For j = 0 To 2
TextBox1.Text = TextBox1.Text & FarmData(i, j) & "---"
Next j      
TextBox1.Text = TextBox1.Text & vbCrLf

I recommend you put a button, it can also be an ActiveX Control, put the following code. You press the button and then fill the textbox.
I removed some lines that were not necessary:

Private Sub CommandButton1_Click()
  Dim sQuery As String, Farm As String
  Dim ReturnData() As Variant
  Dim wsFleetio As Worksheet
  Dim i As Long, j As Long
  Dim sDate As Date
 
  Set wsFleetio = ThisWorkbook.Worksheets("Test")
 
  Farm = wsFleetio.Range("B1").Value
  sDate = DateAdd("yyyy", -1, Date)
  sQuery = "SELECT KillDate, FarmName, LoadType FROM [Loads$] " & _
      "WHERE FarmName = '" & Farm & "' AND KillDate >= " & sDate
  ReturnData = GetMerlinData(sQuery)
  TextBox1.Value = ""
 
  For i = 0 To UBound(ReturnData, 2)
    For j = 0 To 2
      TextBox1.Text = TextBox1.Text & ReturnData(j, i) & "---"
    Next
    TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 3) & vbCrLf
  Next
End Sub

----- --
You forgot to put the code of the GetMerlinData function.
For testing purposes only I used the following function:

Function GetMerlinData(sQuery As String)
  Dim conexion As Object, datos As Object
  Dim bd As String
  
  bd = ThisWorkbook.Path & "\base.xlsx"
  Set conexion = CreateObject("adodb.connection")
  Set datos = CreateObject("adodb.recordset")
  
  conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
    bd & "; Extended Properties=""Excel 12.0; HDR=YES"";"

  datos.Open sQuery, conexion
  GetMerlinData = datos.GetRows
  conexion.Close
End Function

----- --
Note: For some reason the code you posted is on one line, making it difficult to read.
Note Code Tag:
Check this link: How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
----- --

 

[Pardeep Singh]

@DanteAmor You are a genius.. Thank you for the help.

And yes I will improve my question structure for future.

Thank you again, code is working as intended.