report recordsource based on form

[Toxalot]

The forms and reports in the project I am working on are unbound. So
far, I have been using ADO recordsets. I'm using Access 2003 with a
separate FE and BE.

I have a form where the user sets up what data he wants to see on the
report.

In the reports open event, I was reading the info from the form and
then creating an ADO recordset. After banging my head on the wall for
awhile, I found out that you can't set a report's recordset in an mdb.

So how do I go about doing this?

Jennifer

 

[Klatuu]

That is not quite correct. You can't change it while the report is not open.
You can, however, pass information to the report so it will know which
recordset to use.

In 2003, Reports now have an OpenArgs argument like reports. You can use
this method to tell the report which recordsource to use. Then in the Open
event of the report, set the recordsource property based on the value in the
OpenArgs.

Now, with all that said, this seems a bit suspicous. Is this actually
filtering the data for the report? It is very unusual that a report would
use different record sources. All the field names would have to be the same
for this to work at all. If this is, in fact, a filtering issue, use the
Where argument of the OpenReport method to specify the filtering.

 

[Toxalot]

I know the "recordsource" can be set, but not the "recordset".

With my forms I was using ADO recordsets. The recordsource needs to be
a string and I'm not sure how to get the info I have into the format
it needs. I can't just use a local query because the data is in a
separate database. I thought I could just set the recordset and be
done, but now I am not sure what to do.

It's quite a complicated report and I think I am going to have to use
sub-reports to make it work. Would it be better to create temp tables
locally?

I'm really not sure where to go with this.

Jennifer

 

[Klatuu]

A record source points to a recordset. Sorry I wasn't explicit enough about
that. If it is a table, you put the name of the table in the Record Source
property. If it is a query, use the name of the query.

 

[Toxalot]

But I don't have a local table or query. I'm using ADO to connect to a
separate database.

Jennifer

 

[Klatuu]

The table does not need to be local. It will work with an external table. I
use this method myself with an SQL Sever 2005 database. The query can be a
local query.