VBA For Next without For in SQL script

[moone009]

I'm not understanding why this script is not working. Basically I combined the two scripts and then created a user form with a few checkboxes......checkbox 1 do this, checkbox 2 do this. But now I am getting a Next without For error. I tried modifying it with the for i next but no help!


Any ideas??

Private Const msConnStringDB = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=Portal__Prod;Data Source=RPLSQL"
Private Sub cmdBtn_Click()
Dim cn As ADODB.Connection
Dim strSQL As String, i As Integer, ii As Integer
Dim rst As ADODB.Recordset
Dim a, b, sA As String, sCol As String, Rng As Range, InputBox As String

'**********************************************************************************************'
'sCol is where the user will enter the column of the look up value
'**********************************************************************************************'
'' InputBox = Application.InputBox("Insert DB Connections:")
sCol = Application.InputBox("Please input the column of the look up value:")
If VBA.Len(sCol) = 0 Then
MsgBox "No column is selected!"
Else
Set Rng = Range(Cells(2, sCol), Cells(65536, sCol).End(xlUp))
a = Application.WorksheetFunction.Transpose(Rng.Value)
b = a
Set cn = New ADODB.Connection
cn.Open msConnStringDB
For i = LBound(a) To UBound(a)

'**********************************************************************************************'
'To change the look up value change it.viItemName

'**********************************************************************************************'

If CheckBox1.Enabled = True Then

strSQL = "SELECT lo.address1, lo.locationid " & _
"FROM LATransHeader th " & _
"INNER JOIN LATransDetail td ON th.TransHeaderID = td.TransHeaderID " & _
"INNER JOIN LAItem it ON td.ItemID = it.ItemID " & _
"INNER JOIN LAStop st ON td.StopID = st.StopID " & _
"INNER JOIN LALocation lo ON st.LocationID = lo.LocationID " & _
"INNER JOIN LAStatus sa ON td.StatusID = sa.StatusID " & _
"WHERE lo.address1 ='" & VBA.Trim$(a(i)) & "'"
Set rst = cn.Execute(strSQL)
If Not rst.EOF Then
b(i) = rst.Fields(1).Value
Else
b(i) = ""
End If
Next
Rng.Offset(0, 1).Value = Application.WorksheetFunction.Transpose(b)
cn.Close
Set cn = Nothing
End If



If CheckBox2.Enabled = True Then
strSQL = "SELECT lo.address1, lo.Servicenumber " & _
"FROM LATransHeader th " & _
"INNER JOIN LATransDetail td ON th.TransHeaderID = td.TransHeaderID " & _
"INNER JOIN LAItem it ON td.ItemID = it.ItemID " & _
"INNER JOIN LAStop st ON td.StopID = st.StopID " & _
"INNER JOIN LALocation lo ON st.LocationID = lo.LocationID " & _
"INNER JOIN LAStatus sa ON td.StatusID = sa.StatusID " & _
"WHERE lo.address1 ='" & VBA.Trim$(a(i)) & "'"
Set rst = cn.Execute(strSQL)
If Not rst.EOF Then
b(i) = rst.Fields(1).Value
Else
b(i) = ""
End If
Next
Rng.Offset(0, 1).Value = Application.WorksheetFunction.Transpose(b)
cn.Close
Set cn = Nothing
End If




End Sub

 

[Norie]

Is it only the one field name that changes?

If it is why have 2 sets of code?

Also what is the logic of the code? I've tried to work it out but I'm a bit confused.

Do you want to go down a column and for each value in it run the SQL, with the only thing changing in the SQL being the 2nd field in the SELECT clause?

The following will compile but I'm not sure it'll do exactly what you want.

One thing that's really confusing, for me, anyway is what you are doing with the connection and loop.

Private Const msConnStringDB = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=Portal__Prod;Data Source=RPLSQL"

Private Sub cmdBtn_Click()
Dim cn As ADODB.Connection
Dim strSQL As String, i As Integer, ii As Integer
Dim rst As ADODB.Recordset
Dim a, b, sA As String, sCol As String, Rng As Range, InputBox As String
Dim strLookUpField As String
    '**********************************************************************************************'
    'sCol is where the user will enter the column of the look up value
    '**********************************************************************************************'
    '' InputBox = Application.InputBox("Insert DB Connections:")

    sCol = Application.InputBox("Please input the column of the look up value:")
 
    If VBA.Len(sCol) = 0 Then
        MsgBox "No column is selected!"
    Else
        Set Rng = Range(Cells(2, sCol), Cells(65536, sCol).End(xlUp))
        
        a = Application.WorksheetFunction.Transpose(Rng.Value)
        
        b = a
        
        Set cn = New ADODB.Connection
 
        cn.Open msConnStringDB
 
        For i = LBound(a) To UBound(a)
 
            If checkbox1.Value = True Or checkbox1.Value = True Then
 
                If checkbox1.Value = True Then
                    strLookUpField = "lo.locationid"
                End If
 
                If checkbox2.Value = True Then
                    strLookUpField = "llo.Servicenumber"
                End If
 
                strSQL = "SELECT lo.address1, " & strLookUpField & _
                         " FROM LATransHeader th " & _
                         " INNER JOIN LATransDetail td ON th.TransHeaderID = td.TransHeaderID " & _
                         " INNER JOIN LAItem it ON td.ItemID = it.ItemID " & _
                         " INNER JOIN LAStop st ON td.StopID = st.StopID " & _
                         " INNER JOIN LALocation lo ON st.LocationID = lo.LocationID " & _
                         " INNER JOIN LAStatus sa ON td.StatusID = sa.StatusID " & _
                         " WHERE lo.address1 ='" & VBA.Trim$(a(i)) & "'"
                         
                Set rst = cn.Execute(strSQL)
                
                If Not rst.EOF Then
                    b(i) = rst.Fields(1).Value
                Else
                    b(i) = ""
                End If
                Rng.Offset(0, 1).Value = Application.WorksheetFunction.Transpose(b)
                
                cn.Close
                
                Set cn = Nothing
            End If

        Next i

    End If
 
End Sub

 

[moone009]

Well this was built for me by a coworker and then I turned his script into about 20 macro enabled templates but I wanted to combine all of the statements into one user form so I dont have to go from sheet to sheet.

I tested yours out and it only does a vlookup on the first cell and then just replicates everything else.

sorry for the poor explanation Im very new to VBA

City State
New York City NY
LA LA
Orlando Orlando
Madison Madison

 

[Norie]

Well the original code might have done that anyway, and at least it's doing something.:wink:

That's kind of why I was wondering about the loop.

At first I thought it was actually doing something for each row/value but now I'm not too sure.

It actually seems to be doing it once and/or just repeating the same thing over again with the same variables etc.

Actually when I look again it might actually be me that's made the mistake in the placement of the End.

Give me a minute and I'll look closer.

Right, I think I've got it - I did have the Next in the wrong place and there was an End If missing too.

Agaiin, this compiles but I can't be sure it will do what it's meant to - partly because I don't really know that that is.

If VBA.Len(sCol) = 0 Then
        MsgBox "No column is selected!"
    Else
        Set Rng = Range(Cells(2, sCol), Cells(65536, sCol).End(xlUp))
 
        a = Application.WorksheetFunction.Transpose(Rng.Value)
 
        b = a
 
        Set cn = New ADODB.Connection
 
        cn.Open msConnStringDB
 
        For i = LBound(a) To UBound(a)
 
            ' if either of the checkboxs is checked continue
            If checkbox1.Value = True Or checkbox1.Value = True Then
 
             ' if checkbox1 is checked use 'lo.locationid'
 
                If checkbox1.Value = True Then
 
                    strLookUpField = "lo.locationid"
                End If
 
                ' if checkbox2 is checked use 'llo.Servicenumber'
                If checkbox2.Value = True Then
                    strLookUpField = "llo.Servicenumber"
                End If
 
                strSQL = "SELECT lo.address1, " & strLookUpField & _
                         " FROM LATransHeader th " & _
                         " INNER JOIN LATransDetail td ON th.TransHeaderID = td.TransHeaderID " & _
                         " INNER JOIN LAItem it ON td.ItemID = it.ItemID " & _
                         " INNER JOIN LAStop st ON td.StopID = st.StopID " & _
                         " INNER JOIN LALocation lo ON st.LocationID = lo.LocationID " & _
                         " INNER JOIN LAStatus sa ON td.StatusID = sa.StatusID " & _
                         " WHERE lo.address1 ='" & VBA.Trim$(a(i)) & "'"
 
                Set rst = cn.Execute(strSQL)
 
                If Not rst.EOF Then
                    b(i) = rst.Fields(1).Value
                Else
                    b(i) = ""
                End If
            End If
 
        Next i
 
        Rng.Offset(0, 1).Value = Application.WorksheetFunction.Transpose(b)
 
        cn.Close
 
        Set cn = Nothing
 
    End If

 

[moone009]

You are a life saver (aka Time Saver), this is awesome!!! I had one question about the code highlighted in Red. I was just curious of why it has to be written that way. Is that step necessary? I'm definitely not doubting your coding skills at all!!!! Just curious and trying to learn!


I appreciate your help you have no clue how much time this will save me!

THANK YOU!





' if either of the checkboxs is checked continue
If checkbox1.Value = True Or checkbox2.Value = True Then

' if checkbox1 is checked use 'lo.locationid'

If checkbox1.Value = True Then

strLookUpField = "lo.locationid"
End If

' if checkbox2 is checked use 'llo.Servicenumber'
If checkbox2.Value = True Then
strLookUpField = "llo.Servicenumber"
End If
</pre>

 

[Norie]

Well I kind of took a guess there that you would only want the code to run if one of the checkboxes was checked.

The next two Ifs check which checkbox is selected and set strLookupField accordingly.

If neither was checked then strLookUpField would be "" and then the SQL would fail.

Hope that makes sense and I'm actually badly explaining the right part of the code.

PS If it's another part post back.