INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

(OP)
I built the below as a tool to automate some data entry for multiple workbooks at work that references the same centralized .xla file for some custom functions, etc.

The code worked perfectly when I initially build the range checking and initial value checks into the worksheet object, but once I moved it to a module, it's gone rather crazy. I could simply move it back to the worksheet objects, but I was hoping to keep it centralized, so I wouldn't have so much duplication of code.

The highlighted line in the bottom procedure for some reason causes the code to loop back to the second function (also highlighted) rather than just look to the recordset object created within the current procedure. Can anyone point out what I"m missing? What did I break? Thanks for any thoughts.

CODE --> VBA

‘WITHIN THE CURRENTLY OPENED WORKBOOK:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Range(Target.Address), ActiveWorkbook.ActiveSheet.Range("WorkingList")) Is Nothing Then
    Else
        BarCodeQuery Target
    End If
End Sub

‘NEXT 2 PROCEDURES IN A MODULE OF .XLA FILE
Sub BarCodeQuery(Target As Range) 
        If Target.Value Like "####*[-]##[ ][a-z]" Or _
            Target.Value Like "####*[-]##" Or _
            Target.Value Like "####*[-]##[ ][A-Z]" Then
        ' # = Number
        ' * = Wildcard
           ConnectSqlServer Target ' Call code to grab values from SQL Server
        Else
        End If
End Sub


Sub ConnectSqlServer(InputRange As Range)
    Dim conn As Object ' Late Binding
    Dim rs As Object 'Late Binding
    Dim sConnString As String
    Dim strSQL As String
 
    sConnString = "Provider=SQLOLEDB;Data Source=SERVER;" & _
                  "Initial Catalog=DATABASE;" & _
                  "User Id=SqlUserID;" & _
                  "Password=SqlUserPassword;"
                  '"Integrated Security=SSPI;"
    
    Set conn = CreateObject("ADODB.Connection") ' Late Binding
    Set rs = CreateObject("ADODB.Recordset") ' Late Binding
    
    conn.Open sConnString
    
    ' Added "TOP 1" for certain circumstances
    strSQL = "SELECT  TOP 1 Field1, Field2, Field3”
    strSQL = strSQL & "FROM    Database.dbo.Tablec WITH (NOLOCK)"     
    strSQL = strSQL & "WHERE   MyConditions”

    Set rs = conn.Execute(strSQL)
    
    If Not rs.EOF Then
        InputRange = rs.Fields("Field1")
        InputRange.Offset(, 1) = rs.Fields("Field2")
        InputRange.Offset(, 2) = rs.Fields("Field3")
        
    ' Close the recordset
        rs.Close
    Else
        Msgbox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub 

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

Hi Steve,

When you assign a value to InputRange, that changes Target in the BarCodeQuery function call, up thru the Change Event, thus causing it to fire again and so on...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

(OP)
OK, so if I set InputRange separately, then perhaps that'll solve it! I'll try that change. It doesn't have to come from the query, since it is what is used to build the initial query anyway.

Thanks a ton! I'll post back with end results.

(And yes, I should have realized that, but just looked at it too long) blush

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

(OP)
Man! Can't believe I didn't catch that! I just basically quit trying to update that field, and all is well! Thanks a ton, Skip!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

Instead of using Target as the argument, assign the Target.Value to a variable and pass the variable.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Crazy Looping Procedures and ADODB Query Recordset but with no Loop??

(OP)
That could work as well, but in this instance, I already technically have the value there anyway, so it worked best to just leave it alone at that point. Also, a lot of the code pushes the Target rather than Target.Value variable around, b/c there are instances where it checks other Range properties. In this example, I use the range value to determine the other cells to update from the query by using the Range.Offset argument.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close