Not able to assign an ADO recordset as the recordsource/set of a report or form 
Author Message
 Not able to assign an ADO recordset as the recordsource/set of a report or form

Hi there

I have been biting my nails on this one.

I have created a temporary ado table/recordset and can navigate through it, can
add data to it, can assign it to the datagrid object, etc.
But when I try to assign it as the recordset/recordsource of a report or form I
get an errormessage telling me either:

Compile error: Type Mismatch
or
Run-Time error '7965': The object you entered is not a valid Recordset property

Now, I tried to do this on a command button on a form. I tried it in the
report_open and many more things.

Here is a shorter version:

Dim db As DAO.Database
Dim rso As DAO.Recordset
Dim rst As ADODB.Recordset
Dim i As Integer
Dim membercount As Integer
Dim sqlStr As String

Set db = CurrentDb()
set rso = db.OpenRecordset("table1", dbOpenTable)
membercount = rso.RecordCount
rso.Close

Set rst = New ADODB.Recordset

rst.Fields.Append "Name", adVarchar, 60
rst.Fields.Append "Monday", adVarchar, 15
rst.Fields.Append "Tuesday", adVarchar, 15
rst.Fields.Append "Wednesday", adVarchar, 15
rst.Fields.Append "Thursday", adVarchar, 15
rst.Fields.Append "Friday", adVarchar, 15
rst.Fields.Append "StartDate", adDate
rst.Fields.Append "EndDate", adDate

rst.Open

For i = 1 To membercount
  sqlStr = "SELECT ...... from table1 LEFT join table2 .....WHERE ..." & i
  set rso = db.OpenRecordset(sqlStr)
  ...
  rst.Addnew Array("Name", "Monday",......), Array(Trim(rso![last]....)
  ...
  rso.Close
Next i

rst.UpdateBatch
rst.MoveFirst

Now here is where I have tried many different things. I also tried to declare rst
as a global variable in a module with Global rst As ADODB.Recordset and then in
the Report_Open I would try to set Me.Recordsource = rst  (I also tried a
suggestion from unsoftwareag.com as Me.Recordsource = rst.Name) But none of that
works. Then I tried it with a form

DoCmd.OpenForm "Form1", windowmode:acHidden
With Forms("Form1")
 Set .Recordset = rst
 .Visible = True
End With

But that results also in this error '7965'

Everything I read about the temporary ADO recordset indicated that you can do
with it everything you can do with a 'regular' recordset, so why doesn't it allow
me to assign it to a report's or form's recordsource/set?

All the records are in that set properly and all I need now is to run the report
based upon that set. The database is on a network drive and will be accessed by a
few people at the same time, that I why this would work really well, instead of
trying to physically create this table.

Thanks in advance for any advice in this matter. (I've seen other people asking
about this on other forums but with no solutions)

Serge



Mon, 24 Nov 2003 02:39:44 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Assigning a created ado recordset to a sub forms recordsource

2. How to set report RecordSource to ADO recordset?

3. How to set report RecordSource to ADO recordset?

4. Microsoft Access Project 2000-- set a ado recordset as a recordsource

5. Access 2000 How to set RecordSource to ADO Recordset in ADP SQL 7 Project

6. Setting recordset in Access 2000/XP (Set Forms!frmX.recordset = Forms("frmXXXX").recordset

7. ADO recordset errors when trying to set to form recordset

8. ?Using a stored procedure or an ADO recordset as a report recordsource

9. Can we assign form recordsource to existing recordset?

10. Assign a recordset to a forms recordsource


 
Powered by phpBB® Forum Software © phpBB Group