setting a reports recource programatically

[Paterson10987]

is there a way I can change the RecordSource of my report when i go to open it?

I tried

Reports!rptSummary.RecordSource = strSQL,

but if done after you open it an error comes up saying that once the report
is 'printed' the record source cannot be changed.

The SQL that i want it to have come from a query of sub queries that changes
often. I don't suppose I can get just the WHERE part of the SQL and add that
to the 'where' parameter of the docmd.openreport. But it would not let me
filter my form this way, thats why I change the forms record source, so would
it work with the report?

I don't know. Help.

 

[dymondjack]

You can use the Where Condition of the DoCmd.OpenReport method to do this,
providing that the reports recordsource is correspondant with the Where
condition you supply (it should be anyway, but...)

I do this with a number of reports, works fine.

On the other hand, I'm pretty sure I've changed the actual RecordSource of a
report via vba... I think it needs to be in the OnOpen event though.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Paterson10987]

FYI:

strSQL =
SELECT SpareParts.*
FROM SpareParts
WHERE ( [Enter Part Filter]
AND (SpareParts.ID)
In (SELECT PartSuppliers.PartID FROM PartSuppliers
WHERE ( [Enter Part Suplier Filter] ))
AND (SpareParts.ID)
In (SELECT PartLocations.PartID FROM PartLocations
WHERE ( [Enter Part Location Filter] )));

 

[Jim Burke in Novi]

Dymondjack has it right, just do it in the report Open event proc. Just use

Me.recordsource = strSQL.

strSQL would have to be a public 'global' variable. I know setting the
recordsource this way in the open event works, I do it in a few of my reports.

 

[dymondjack]

strSQL would have to be a public 'global' variable.

Couldn't he do it with an OpenArg also?


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Jim Burke in Novi]

In Access 2000 (which is what I use) there is no OpenArg argument for reports
- maybe there is in later versions. There's only arguments for report name,
view, filter and where condition. If there is an OpenArg in the version he's
using, then that would be the way to go.

 

[dymondjack]

Ahh... I didn't know A2k didn't have it


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain