Dynamics GP - Modifier, VBA Visual Basic, Sanscript, ADO Loop SQL Query to populate a dynamic drop-down list, and maintain arrays of result values

    'Global Variables
    Dim TheLoan(1 To 50)
    Dim TheRemLoanAmt(1 To 50)

Private Sub DDLM158_Changed()
If DDLM158 > 1 Then
PONumber = TheLoan(DDLM158 - 1)
LoanAmt = TheRemLoanAmt(DDLM158 - 1)
Else
PONumber = ""
LoanAmt = 0
End If
End Sub

Private Sub Purchases_Changed()
If VendorID > "" And CCur(Purchases) > CCur(IIf(LoanAmt = "", 0, LoanAmt)) And LoanAmt > "0" Then
MsgBox "Purchases cannot exceed Loan Amount of " & CCur(LoanAmt) & ". Please enter a new amount to purchase."
Purchases.Focus
End If
End Sub
Private Sub VendorID_Changed()
If DocumentType = 3 And VendorID > "" Then
DDLM158.Enabled = True
    'Dim CompilerApp As New Dynamics.Application
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String
    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
   
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & "clear '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "add item "" <Available Loans>"", 0" & vbCrLf
    CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "'(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry = 1;" & vbCrLf
'Populate dropdown
    Dim objRec
     Dim objConn
     Dim cmdString
     Dim i
   
     i = 1
     Set objRec = CreateObject("ADODB.Recordset")
     Set objConn = CreateObject("ADODB.Connection")
   
     objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DEVDB;Initial Catalog=TWO;User Id=sa;Password=pw"
     objConn.Open
   
   
     cmdString = "SELECT LoanID, RemLoanAmt, VENDORID FROM BI_AvlblLoans where (VendorID='" + VendorID + "')"
   
     Set objRec = objConn.Execute(cmdString)
   
       If Not (objRec.EOF And objRec.BOF) Then
            Do While Not objRec.EOF 'Start looping through the records
                'Grab record Data
                TheLoan(i) = RTrim(objRec!LoanID)
                TheRemLoanAmt(i) = objRec!RemLoanAmt
                'add a dropdown line
                If Not VendorID.Empty Then
                CompilerCommand = CompilerCommand & "add item " & """" & TheLoan(i) & " | " & TheRemLoanAmt(i) & """" & ", " & i & vbCrLf
                CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
                i = i + 1
                End If
                objRec.MoveNext
            Loop
        End If
   
    objConn.Close
   
    ' Execute SanScript
    CompilerApp.CurrentProductID = 0 ' DYNAMICS
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
Else
DDLM158.Enabled = False
End If
    
    
End Sub
Private Sub Window_AfterOpen()
DDLM158.Enabled = False
    'Dim CompilerApp As New Dynamics.Application
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String
    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
   
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & "clear '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "add item "" <Available Loans>"", 0" & vbCrLf
    CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "'(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry = 1;" & vbCrLf
    ' Execute SanScript
    CompilerApp.CurrentProductID = 0 ' DYNAMICS
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
End Sub