Recordset Property of a Form

Posted on 2005-04-27
Last Modified: 2012-06-27
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("Number") = "1"
        rs("Number") = "2"
    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?

Question by:spencerturbine
    LVL 44

    Expert Comment

    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.

    LVL 65

    Expert Comment

    by:Jim Horn
    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.
    LVL 16

    Expert Comment

    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.
    LVL 65

    Expert Comment

    by:Jim Horn
    Here's a thread I discovered on this topic while doing CV stuff...

    Author Comment

    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?

    LVL 16

    Accepted Solution

    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.

    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now