Dynamic record source

[dixxy]

Hello,

I am trying to minimize the amount of reports to build.

I would like to knbow how I could use the same report, but with two different record source, not necassaraly two different queries (I am trying to minize the amount of queries in the database as well).

The data is almost the same it just come from differnet tables.

I am thinking something like:

If open from form 1 then
select from this and this table, with this and this criteria

If open from form 2 then
select form other table , etc.etc

I am almost sure this can be done, butr not sure of the proper way to do it and waht to dim how and where...

Thanks in adanvce

Thanks,

Sylvain

 

[CosmoKramer]

Check out Access help for the OpenReport method. You can define which query you want to be the report's record source with this statement.

Hoc nomen meum verum non est.

 

[dixxy]

Thanks Cosmos, but my Access help doesn't tell me much regarding waht I am trying to accomplish...


Thanks,

Sylvain

 

[CosmoKramer]

Access help says the syntax is:

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

The argument for the wherecondition can be used like a WHERE clause in an SQL statement. Dim a string variable to hold the wherecondition then pass it to the OpenReport statement. For example:

Dim strCritrtia As String
strCriteria = "[txtField] = '" & [YourCriteriaField] & "'"
DoCmd.OpenReport "YourReport", acViewNormal, , strCriteria

Note the single quotes

(')

used if the criteria is a text field. If it's a number field omit the single quotes.....


Hoc nomen meum verum non est.

 

[dixxy]

Cosmos,

I am not sure what the ="[textField] = '" is supposed to be. This is the where clause of my SQL ...WHERE (((tblJobs.JobNumber)=[Forms]![frmJobs]![JobNumber]) AND ((tblRooms.Print)=True))"

Thanks,

Sylvain

 

[GoDawgs]

If you're really pulling from two different tables then I'd set up 2 different reports. You don't have to store the queries that are the recordsources for reports, you can create them and save them specifically to that report. Hope that helps.

Kevin

 

[CosmoKramer]

I love it when all of the information finally comes out ( multiple tables?)

I would go with Kevin's suggestion. You can use one report, but utilize multiple queries.

Hoc nomen meum verum non est.