• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Recordset Property of a Form

I have a form with one text field called Number.

I am trying to set the forms recordset property to that of a rs that I created. Here is the code:

Private Sub Form_Open(Cancel As Integer)

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Fields.Append ("Number"), adChar, 1
    rs.Open
    rs.AddNew
        rs("Number") = "1"
    rs.Update
    rs.AddNew
        rs("Number") = "2"
    rs.Update
   
    Set Me.Recordset = rs
   
End Sub


I am getting  a 7965 error: The object you entered is not a valid recordset property.

Can someone she some light on this for me?

Thanks
0
spencerturbine
Asked:
spencerturbine
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Arthur_WoodCommented:
what version of Access are you using?  

When you assign the recordset from within a MDB, you are assigning a DAO type recordset, but your code is creating an ADO Recordset.  Look up the Recordset property in the Access Help System.

When you work with the Recordset property in an Access Data Project (ADP), then you are working with an ADO-type recordset.

AW
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi spencerturbine,

AFAIK you can't assign Me.Recordset to a recordset object.  It's Me.RecordSource, and it has to be a valid table or query.

Hope this helps.
-Jim
0
 
GreymanMSCCommented:
Actually, jimhorn, the code appears to work just fine in Access 2002.  The recordset is created and bound to the form with no trouble.  The only problem I'm experiencing with it is that, with an anonymous RecordSource, I cannot bind a control to the recordset's fields.

I suspecy that Arthur_Wood is on the right track though.  Prior to Access 2002, the code would not work.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's a thread I discovered on this topic while doing CV stuff...
http://www.experts-exchange.com/Databases/MS_Access/Q_21297812.html
0
 
spencerturbineAuthor Commented:
What I am not understanding is that this code works in Access 2000

Private Sub Form_Load()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open ("SELECT * FROM T")
    Set Me.Recordset = rst
End Sub

So why wouldn't the recordset I created work?

0
 
GreymanMSCCommented:
The first recordset you created is a virtual recordset.  It does not exist as part of the database connection, but only in memory.  As a virtual recordset it has an empty .Source property.  The form then inherits this as an empty .RecordSource property, and it just is not designed to be able to work with this.  (The data exists behind the form, and you can navigate through the record set, and access the field data through the .Recordset property, but you cannot bind controls and such.)
 
The second recordset exists within the database collections, and has a .Source property (the SQL command string).  The form then inherits this property and thus has all the information it needs to act as an interface it's controls and the recordset.
0
 
spencerturbineAuthor Commented:
Just a final comment,

When I assign the form's recordset property to the rs I created is shows #Error in the bound text box.


But anyway that is disappointing to say the least.

Thanks for the good answer.

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now