SQL Parameter Query - Rowset Position Cannot be Restarted

I have the following code:

It works in that my query executes and if I remove the .MoveFirst statement (bold below), but I need that statement in there as my records are sorted in order and I refer to it throughout the program.

This is the first time I open the recordset and when it gets to that .movefirst statement below I get

"An Error Has Occurred - Rowset position cannot be restarted"

Any idears? I notice using a debug.print that it's not setting the cursor type/location to what I specify

Code:

---

strSQL = "My QUERY GOES HERE"
               
 Dim objCommand As ADODB.Command
 Set objCommand = New ADODB.Command
 
 With objCommand
  .ActiveConnection = SQLCon
  .CommandType = adCmdText
  .CommandText = strSQL
  .Prepared = True
  .Parameters.Append .CreateParameter("ClinicDate", adDate, adParamInput, , dtDate)
  .Parameters.Append .CreateParameter("Institution", adChar, adParamInput, 4, strInstitution)
     

  rsGetTodaysData.CursorType = adOpenDynamic
  Set rsGetTodaysData = objCommand.Execute
  Set objCommand = Nothing

End With
 
With rsGetTodaysData
  If .BOF <> True And .EOF <> True Then
      .MoveFirst
        Do Until .EOF = True
            If FormInUse.Name = "frmMain" Then

---

Think I got it.

Changed the line "Set rsGetTodaysData = objCommand.Execute" to say "rsGetTodaysData.Open objCommand". That runs my query and by checking the cursor location and type with a debug.print statement, it also sets those appropriately.

Don't you need rsGetTodaysData.CursorLocation=adUseClient?

I put that in too.

Everything i found to that Error-Message points to CursorLocation=adUseClient missing,
since adUseServer is the default if omitted

I had a Set rsGetTodaysData = New Recordset statement at the top of that sub.

I don't know why, but even though I specified a cursor location and type it wasn't "taking". Not until I changed that statement to rsGetTodaysData.Open objCommand did it work. I had the adUseClient in there earlier and it wasn't working. That's part of it I know but the way I was opening the recordset was causing issues somehow.

Consider the following lines from the original snippet:

rsGetTodaysData.CursorType = adOpenDynamic
Set rsGetTodaysData = objCommand.Execute

The second one actually creates a brand new recordset. Whatever properties you set before that are gone. It's unnecessary even to allocate a recordset before setting the reference to a brand new one.

How can you control the CursorLocation of this newly minted recordset?

Just set Connection.CursorLocation to whatever before Execute-ing the command. Must admit this is an obscure API design by ADO team, probably an after-thought.

cheers,
</wqw>