Record Source for reports and SQL Statements

[forweefolks]

I'm working on a really complicated database and building the reports for it at the moment.

The problem is that I have a lot of reports with miniscule changes, and all these have to be triggered by commandbuttons so that users can access them from a form. What I want do is to have a limited number of pre-made and pre-designed reports that i could modify, and thus reuse, in code.

The obvious way to do this would be to modify the SQL statements underlying the reports in code. As far as the helps take me, I understood this could be done by setting different record sources for the reports. It however does not work (I've been banging my head to the wall for the most part of the day (It's 7.30 P.M. where I live)).

The database is a usual database, not a project. Can i modify the recordsource sqls in code? Can modify queries in code? Is there a way to do what I'm doing?

I would appreciate all help.

Mika

 

[programmerchick]

Are you saying your db creates a new report everytime you click the command button?
Sharon Mihal
smihal@mpea.com

 

[forweefolks]

yes, more or less.

I would be using a readymade report format (the best word I can think of is template) and modify it in the code to accommadate the info produced by the record source query(the thing would have to look relatively good).

so, any way to change the recordsource after using the OpenReport action? I get the message that says something like "can't change the record source after the report has started printing".

Mika

 

[Francescina]

There are different possibilities to modify the recordsource of a report:
1.) with the OnOpen event
2.) with the WhereCondition of the Docmd.openreport method

You could also use a recordset (with its name-property->tip of the ACCESS ONLINE ENCYCLOPEDIA at

http://www.unsoftwareag.com)

instead of a SQL-string.

Your error message has been triggered by the OnPrint event.

 

[programmerchick]

If you are calling the same report from different places with different data, I would use a select case statement based on a variable that I passed to the on open event.
Ex...
sub report_open(i as integer)
Select Case i
case 1
me.recordsource = ("Select * from .....&quot
case 2
me.recordsource = ("Select * from.....&quot
End select
end sub

 

[dixxy]

I am trying to do something similar to this.

I want to open the same report from two different forms with different data in it. Would your example be the way to do this?

Also my report has a subreport in it, how could i go about getting it's data?

Thanks,

Sylvain