create a recordset object from a forms recordset

[clapper62]

I have a form with data I populated with a ADO recordset from SQL server
what I would like to do now is create a recordset object from the data in the form

something like this

Dim rst As New adodb.Recordset
Set rst = Me.Recordset
Set rst = Nothing

this code runs without error
but I dont think it really works
because when I try to do anything with the recordset
such as .sort or .recordcount I get an error

is it possible to do what I'm trying to do?

 

[PHV]

Why this ?
Set rst = Nothing


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I get an error

Any chance you will be will to tell us what the error is? Also can you show us where you bind the form's recordset to and ADO recordset? Can you provide some real code. All you show is assigning a variable and setting it to nothing. Do not really understand that either.

 

[clapper62]

The Set rst = Nothing
was after some other code like this

Dim rst As New adodb.Recordset
Set rst = Me.Recordset
rst.Sort = "User_Id ASC"
Debug.Print rst.RecordCount
Set Me.Recordset = rst
Set rst = Nothing

this code gives an error at

Debug.Print rst.RecordCount

runtime error 3704
operation is not allowed when object is closed

if I take this statement out it throws another error at

Set Me.Recordset = rst

run-time error 3265
item not found in collection

 

[MajP]

Again you need to show where you assign the forms recordset to an ado recordset. Cannot help unless you show the assignment.

 

[clapper62]

MajP

I thought that
Set rst = Me.Recordset
was the assignment

remember the original question was if it is even possible
to assign a forms recordset to a recordset object and then
perform operations on that recordset object

I know that I can do what i want to do by opening a connection to the server and
then create the recordset with a SELECT statement that includes a ORDER BY to sort the records
the way I want but..

what I was wondering is if I could do the same thing (sort the records) without creating
a connection to the server again

if I am being vague it's again because I'm not even sure whether this is possible

 

[PHV]

By default, a Form's recordset is ADO, not ADO.
So, again, how you assign the forms recordset to an ado recordset ?
BTW, are you playing with an adp ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

That is not an assignment of a form's recordset to a recordset. That is the reverse.

There is no way to return an ADO recordset from a form unless you assign it an ADO recordset first. Me.Recordset will return a DAO recordset if the form is populated by any other means. It cannot be cast from DAO to ADO.

remember the original question was if it is even possible
to assign a forms recordset to a recordset object and then
perform operations on that recordset objec

It was?? Where did you ask that? You just showed some code and said you got an unspecified error.

Yes you can create and ADO recordset and then assign it to a form. Then you can sort and filter it. What are you really trying to do? You can do all that regardless.

 

[PHV]

Sorry for the typo:
By default, a Form's recordset is DAO, not ADO.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[clapper62]

PHV
>By default, a Form's recordset is ADO, not ADO.
I can only assume you mean a forms recordset is DAO, not ADO

>So, again, how you assign the forms recordset to an ado recordset ?
I don't know how to assign a forms recordset to an ADO recordset, I don't even know if it can be done that is the question

I can only think 2 things at this point
Either it's not possible to do this (or maybe I need to Dimension my recordset variable as a DAO recordset)
or
I am not explaining what I want to do clearly enough and lack the knowledge to do so


>BTW, are you playing with an adp ?
No

 

[PHV]

So, replace this:
Dim rst As New adodb.Recordset
with this:
Dim rst As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

As I said it is absolutely possible to assign an ADO recordset to a forms recordset.

 

[clapper62]

PHV
ok this code
Dim rst As DAO.Recordset
Set rst = Me.Recordset

Debug.Print rst.RecordCount

give error Type mismatch at
Set rst = Me.Recordset

MajP
I think what your saying is the reverse of what I'm trying to do, assign a forms recordset to a recordset object
if not then please just a small snippet of code as an example

 

[clapper62]

Just to be thorough I have no problem with this code
here is the code that populates the data in the form

Dim strConnectionString As String
Dim objConnection As adodb.Connection
Dim rst As New adodb.Recordset
Dim strSQL As String

'create the connection to the server
strConnectionString = SouthConnectionString
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open

'create the recordset
strSQL = Me.tboxSql
rst.Open strSQL, objConnection, adOpenKeyset, adLockReadOnly

Set Me.Recordset = rst

rst.Close
objConnection.Close

Set rst = Nothing
Set objConnection = Nothing

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[MajP]

I think what your saying is the reverse of what I'm trying to do, assign a forms recordset to a recordset object
if not then please just a small snippet of code as an example

Private Sub Form_Load()
Dim rsClone As adodb.Recordset
Dim strSql As String
Set RS = New adodb.Recordset
strSql = "SELECT * from Employees"
RS.CursorLocation = adUseClient
RS.Open strSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set Me.Recordset = RS
Set RS = Nothing
End Sub

Private Sub EmployeeID_Click()
Dim RS2 As adodb.Recordset
Set RS2 = Me.Recordset
RS2.Sort = "FirstName"
Set Me.Recordset = RS2
End Sub

1. open an ado recordset
2. set the forms recordset to the ado recordset
3. create a new rs and assign it to the forms recordset

works for me.

 

[clapper62]

Thank you borh for your time spent

MajP
your code
Private Sub EmployeeID_Click()
Dim RS2 As adodb.Recordset
Set RS2 = Me.Recordset
RS2.Sort = "FirstName"
Set Me.Recordset = RS2
End Sub

is actually what I'm interested in.


you say it works for you unfortunately it doesn't for me
I am using Access 2003 if that makes a difference

here is my code
Private Sub cmdSortByUser_Click()
Dim RS2 As adodb.Recordset
Set RS2 = Me.Recordset
RS2.Sort = "User_Id"
Set Me.Recordset = RS2

End Sub

I get "run-time error 3265 item cannot found in the collection corresponding to the requested name or ordinal"
Set Me.Recordset = RS2

User_Id is a valid field name

"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[MajP]

Can you try another field? That error message sure looks like it does not like that field name.

 

[clapper62]

I tried several different fields with same results

I also tried
RS2.Sort = ""

which I read should reset the order to it's original state
but I still get the same error

 

[MajP]

does setting a client side cursor make a difference?
RS.CursorLocation = adUseClient

 

[clapper62]

here's my newly attempted code

Private Sub cmdSort_Click()
Dim RS2 As adodb.Recordset
Set RS2 = Me.Recordset
RS2.CursorLocation = adUseClient
RS2.Sort = ""
Set Me.Recordset = RS2
End Sub

and that gives me an error at
Set Me.Recordset = RS2

Run-time error
Method 'Recordset' of object '_Form_MyFormName' failed

I will note that your code seems to get data from a table that is in the same database as your form whereas
mine gets its data from an SQL Server database and I create the recordset with this line as shown in above post

rst.Open strSQL, objConnection, adOpenKeyset, adLockReadOnly



if that makes no difference then I don't know why your code works but mine doesn't