Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO recordset problem (Access 2000 9.0.2720)

    Hi everyone,

    I am having a go at making a database for a charity and having trouble with ADO recordsets and subforms etc. For instance something very similar to this code has worked for me on another form, but this version is failing on the 'Set Me.SbfMain.Form.Recordset = rstSub' . I have confirmed that the recordset has records in it. The error code is 2455 and the message: "You entered an expression that was an invalid reference to the property recordset"
    Here is the code
    <hr>
    Dim cmd As ADODB.Command
    Dim rstSub As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim prm2 As ADODB.Parameter

    Set rstSub = New Recordset
    rstSub.CursorLocation = adUseClient
    rstSub.LockType = adLockBatchOptimistic
    Set cmd = New Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qryLogFunderDates"

    Set prm = cmd.CreateParameter
    prm.Type = adDate
    prm.Size = 25
    cmd.Parameters.Append prm
    cmd.Parameters(0).Value = CDate("01/01/2001") ' fromdate

    Set prm2 = cmd.CreateParameter
    prm2.Type = adDate
    prm2.Size = 25
    cmd.Parameters.Append prm2
    cmd.Parameters(1).Value = CDate("01/01/2004") ' todate
    rstSub.Open cmd
    Set Me.SbfMain.Form.Recordset = rstSub

    End Sub<hr>

    The fact that the record set has records in it makes me think that I do not need to go into too much detail about the design. The subform starts off with a similar query in datasheet view and I am just trying to subset by date with the new query that has a couple of new fields in the WHERE clause.

    Thank you for any assistance that anybody can offer.

    <font face="Comic Sans MS">Michael Beard</font face=comic>

    West Midlands Job Hunt : Job Hunter Blog : EarlsfieldGuide.co.uk

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    If you don't receive a reply, you could perhaps post a stripped down copy of the database.
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (We can conver it back when opening the database.)
    <LI>Attach the zip file to a reply.[/list]That would enable us to see what happens, and to experiment.

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    Thanks Hans. I thought that it would be a fairly common and/or trivial problem. But you are right, maybe posting the database will help.
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    Hmm, it seems that attaching the database to the previous post has not helped prompt people to assist. Is it such a tough question?

    I am thinking that I might be able to work around it by refering to the from and to date text boxes within the one query. Right now I am struggling with the empty field situation, ie select all records if no dates entered. I had it cracked with an OR clause in the query builder, but then Access decided to move it to a different column. This broke the query, in that the empty fields produced no records rather than all records. I was looking into this in SQL, did a test run and found that I had lost all of my query definition, it was back to a blank screen!

    Guess I'll keep digging, I am sure that the parameter query is a neater solution but that is very much a dead end it seems?

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    The Source Object for the "subform" is a query. (See attached pic) You cannot assign a recordset to a query, queries don't have recordsets. Recommend create simple form based on query and use the form as Source Object for the subform control on main form, then you should be able to set a recordset to the form's Recordset property.

    HTH
    Attached Images Attached Images

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    Thanks Mark.

    I have tried to set the record source to the new query, but it would seem to be one of Access's quirks that you cannot do that through VBA. In which case, the form in a subform does make sense.

    thanks again <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Michael Beard

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    PS - Forgot to mention, if you're using an ADO recordset to set the form's Recordset property, keep in mind, unless you're using SQL Server data (with an adUseClient cursor), the form will be read-only. To quote Help file briefly:

    "When a recordset is asked for in a Microsoft Access database (.mdb), a DAO recordset is returned, in a Microsoft Access project (.adp), an ADO recordset is returned. The read/write behavior of a form whose Recordset property has been set using Visual Basic is determined by the type of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the recordset identified by the property."

    <table border=1><td>Recordset type</td><td>Based on SQL data</td><td>Based on Jet data</td><td>ADO</td><td>Read/Write</td><td>Read Only</td><td>DAO</td><td>N/A</td><td>Read/Write </td></table>

    See Help (Recordset Property) for more details. So if you want the subform to be updatable, and you are using a Jet (.mdb) database, you may have to resort to using a DAO recordset instead of ADO. There have been examples of DAO recordsets based on parameterized queries posted in previous threads on the subject.

    HTH

  8. #8
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO recordset problem (Access 2000 9.0.2720)

    That sure does explain a few things, except why my "Running Microsoft Access 2000" book from Microsoft themselves all but dismisses all other data access systems except ADO when it is still central to Access? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Oh, come back RPG400, all is forgiven <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO recordset problem (Access 2000 9.0.2720)

    There is a certain amount of DAO actually built into Access, more in XP than in 2000. Remember that ADO doesn't really know much about Access objects because it is intended to manipulate data, not interface. Interface objects are DAO-based, even if it isn't apparent, so some things still require DAO.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •