VBA ADO Recordset Loop Through Values

[Huey72]

Hi everyone - I'm using vba to create an ADO recordset based on a named range in the current workbook, and I'm using this overall procedure to create an MS word form with content controls. I am able to create the connection and recordset, when I do a recordcount I get 30 records, which is what I expect, but for some reason, the assignment of the value from the record isn't working - I've tried rs(0), rs("Service_Detail_2"), rs.Fields("Service_Detail_2").Value, and rsProcedure![Service_Detail_2] where Service_Detail_2 is the column header in the dataset, but I'm getting a null result each time, and an error message of Invalid Use of Null when I try to assign the expected result to a variable for test purposes. I can't seem to figure this out in any searching and I'm guessing it's just a syntax error or something little that is blocking this - any help is greatly appreciated! What's also interesting is if I add the line of code - On Error Resume Next - above the TestMe variable that triggers the null error, I would expect the procedure to run through each of the 30 records and end without providing the result I want, but it goes into an infinite loop. This makes me thing the problem isn't with the commands I'm trying to get the variable, but actually with something else related to the recordset or loop. Thanks!

Here's the sample code section for reference:

Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public myRecordCount As Long

'Create the connection...
Set con = CreateObject("ADODB.Connection")
con.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

'Open the recordset...
sql = "SELECT Distinct Service_Detail_2 FROM [Procedures]" 'Procedures is the named range for the dataset in the current workbook

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, con, adOpenStatic, adLockReadOnly

'If the recordset is empty...
If rs.EOF Then
Exit Sub
End If

'With the recordset...
With rs
.MoveLast
myRecordCount = .RecordCount
.MoveFirst

Do Until .EOF ' Do until/loop until end of field i.e., last record
'Test the result to confirm ability to pull the value from the recordset for assignment in Word
Dim TestMe As String
TestMe = rs(0) 'rs("Service_Detail_2") 'rs.Fields("Service_Detail_2").Value 'rs![Service_Detail_2]
Loop
End with

'Clean up the recordset...
rs.Close
Set rs = Nothing

'Clean up the connection....
con.Close
Set con = Nothing

 

[RAYLWARD102]

like this:

    RS.Open Sql, con, adOpenForwardOnly, adLockReadOnly
    If Not RS.EOF Then
        Do While Not RS.EOF
            
            RS.MoveNext
        Loop
    End If
    RS.Close