I'm having difficulty using a For Each...Next Statement.
Basically I have created an ADO recordset, and now I want to call another function on each row.
This is my current code:
Public Function GetEmployers()
Dim sSQL As String
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim rs As ADODB.Recordset
Set adoConn = CurrentProject.Connection
Set adoCmd = New ADODB.Command
sSQL = " SELECT Emp_ID FROM dbo_tblEmployer WHERE BArea ='" & "Animal Care" & "'"
.ActiveConnection = adoConn
.CommandType = adCmdText
.CommandText = sSQL
Set rs = adoCmd.Execute
If rs.EOF = False Then
For Each rs.GetRows
Set rs = Nothing
Everytime I debug it displays the message 'syntax error' and highlights the For Each line.
I take it that I shouldn't be using the rs.GetRows, but what else can I use to run through each row in the recordset?
The help states it repeats a group of statements for each element in an array or collection.
The syntax is often something like
for each <item> in <some collection or group>
GetRows is neither an item nor a collection, though it returns a variant whose value is a two dimensional array - look it up in the help files.
When you need to loop a recordset, then that's what you do. Start from one position (start?) and loop to another position (end?), as demonstrated, unless you can achieve your objective through a query.