Link to home
Avatar of spencerturbine
spencerturbine

asked on

Can't Bind recordset to report in Access

I am trying to create a recordset and bind it to a report but I get an error

the first error I understood to be that in an mdb I can't bind an RS to a report so I created an adp and in and entered the following VBA code.

The error I get now is runtime error 91 Object variable or With block variable not set.

How can I accomplish this task?




Public Sub Report_Open(Cancel As Integer)
Dim rs As Recordset
Set rs = New Recordset
 
rs.Fields.Append "myField", adChar, 255
rs.Open
'Add a row to the in-memory Recordset
rs.AddNew
rs("myField") = "Hey Joe"
rs.Update
Me.Recordset = rs

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Explain without using technical terms what you wish to do here.
spencerturbine,

OK, let's back up a bit.

"I am trying to create a recordset and bind it to a report but I get an error
the first error I understood to be that in an mdb I can't bind an RS to a report so I created an adp"
Who says that if it won't work in an MDB, that it will work in an ADP?

It is a drastic step to change your entire database format just to bind a Report's Recordsource to a Recordset.

While it is true that you cannot do this directly, you can simply set the Report's RecordSource to the same SQL string that the recordset would use.
Here is a basic example:
Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
    strSQL = "SELECT MovieTitle,Price FROM tblDetailVideos WHERE Price > 20"
    Me.RecordSource = strSQL
End Sub

Moving forward...
Even ignoring the Report RecordSource issue, the code itself does not even complie or run.
It fails on:
    Set rs = New Recordset

So I guess my question is the same as Jim's...
;-)

JeffCoachman
Avatar of spencerturbine
spencerturbine

ASKER

The code I posted errors for me on the Me.Recordset = rs part.

I think the code explains what I am trying to do (even though it may not be functional)

I just want to create a recordset add some data to it and then display it on a report.

I don't want to involve tables or sql statements.

"Who says that if it won't work in an MDB, that it will work in an ADP?"

I read that on a few other sites when I was researching how to do this.
spencerturbine,

1. Again as far as I know you cannot feed a recordset directly into a Report's record source.
If you can do it with an ADP, then you will have to research that.

2. I have no Idea why the code throws the error.
I have never had a need to create a recordset in memory.
I have seen "Arrays" used in this way, but not recordsets.

JeffCoachman
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
LSM,

Thanks,

Can you do something similar for an MDB?

Jeff
Yes, so long as it's 2002 or greater. The Recordset property of forms, reports and even combos and listboxes support setting to a valid ADO recordset.