VBA ADO Recordset Loop Through Values

Huey72

New Member
Joined
Nov 6, 2019
Messages
32
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
527
like this:
VBA Code:
    RS.Open Sql, con, adOpenForwardOnly, adLockReadOnly
    If Not RS.EOF Then
        Do While Not RS.EOF
            
            RS.MoveNext
        Loop
    End If
    RS.Close
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,933
Messages
5,834,450
Members
430,287
Latest member
Shinoy98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top