ADO recordset problem (Access 2000 9.0.2720)

Topic

New Reply

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
[indent]

---

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

---

[/indent]

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.

Michael Beard

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

Reply | Quote

Replies

If you don’t receive a reply, you could perhaps post a stripped down copy of the database.

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• 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.)
• Attach the zip file to a reply.
  [/list]That would enable us to see what happens, and to experiment.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Michael Beard

Reply | Quote

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.”

Recordset type	Based on SQL data	Based on Jet data
ADO	Read/Write	Read Only
DAO	N/A	Read/Write

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

Reply | Quote

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?

Oh, come back RPG400, all is forgiven

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Oh, come back RPG400, all is forgiven

Reply | Quote

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.”

Recordset type	Based on SQL data	Based on Jet data
ADO	Read/Write	Read Only
DAO	N/A	Read/Write

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

Reply | Quote

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

Michael Beard

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote