Reports don't work in MDE

[Guest]

In Office 2003, I'm using the following code to alter the contents of a
report so I don't have to create several reports that only vary by user, a
date, etc.:

DoCmd.OpenReport "rptHoldChecks", acViewDesign 'open in design to edit
Reports.Item("rptHoldChecks").RecordSource = sSQL 'change the SQL string
DoCmd.OpenReport "rptHoldChecks", acViewPreview 'open normally to show

This code simply alters the SQL behind the report before it is run. Is this
considered a "clean" way of accomplishing this? Or is there a better way to
reuse a report by feeding it different parameters?

The only reason I'm asking is that I compiled this FE and everything works
in the MDE EXCEPT the reports! Any help appreciated, TIA.

 

[Douglas J. Steele]

You cannot open reports in Design View in an MDE: the Design View doesn't
exist.

On the other hand, you should require Design View simply to change the
RecordSource.

 

[Allen Browne]

Design view is never available in an MDE.

In most cases, you can use the WhereCondition of OpenReport to apply a
filter. Here's an example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If that's not adequate, you can reassign the RecordSource property of the
report in its Open event.

If that's still not adequate, you can assign the SQL property of the
QueryDef the report is based on before you OpenReport. (This technique can
be useful for subreports where Report_Open is not useful.)

 

[Stefan Hoffmann]

hi XP,

DoCmd.OpenReport "rptHoldChecks", acViewDesign 'open in design to edit
Reports.Item("rptHoldChecks").RecordSource = sSQL 'change the SQL string
DoCmd.OpenReport "rptHoldChecks", acViewPreview 'open normally to show

Without using the AutoCompact feature this will bloat your database.

This code simply alters the SQL behind the report before it is run. Is this
considered a "clean" way of accomplishing this? Or is there a better way to
reuse a report by feeding it different parameters?

Use a query as record source. The query can be modifed in a MDB:

CurrentDb.QueryDefs.Item("ReportQuery").SQL = sSQL

Or use a proxy function for your parameter, so the record source looks like

SELECT *
FROM Table
WHERE Field=fctParameter()

With fctParameter returning the appropriate part from sSQL.

The only reason I'm asking is that I compiled this FE and everything works
in the MDE EXCEPT the reports! Any help appreciated, TIA.

A MDE is like MDB with compiled code only. The objects with code cannot
be modifed in the design mode anymore.


mfG
--> stefan <--

 

[John Spencer]

How are you changing the report's recordsource. If all you are doing is
specifying criteria, then you should be able to do that in the
DoCmd.OpenReport. If you are specifying a different prebuilt query, you
should also be able to do that.

Code snippet to have the report only show people named "Smith"
Dim strWhere as String
strWhere = "CustomerLastName = 'Smith'"
DoCmd.OpenReport "rptHoldChecks, acViewPreview, ,strWhere

If you have saved queries that you want to run with the report
Dim strQueryName
strQueryName = "qHoldChecksThisMonth"
DoCmd.OpenReport "rptHoldChecks, acViewPreview, strQueryName

Check out the VBA help on the OpenReport method.

 

[Albert D. Kallal]

Is this

considered a "clean" way of accomplishing this? Or is there a better way
to
reuse a report by feeding it different parameters?

Throwing the reprot into desing mode to accomplish this task is a VERY poor
desing decision.

Modifying reprots in design mode at runtime will cause EXCESSIVE BLOAT in
use of the aplcations.

Worse, is that you can't have occaosonal multi-user access, since hte reprot
is being modifed by code. However, for any type of multi-user setup, you
have to split the database anyway.

First, you don't need to throw teh reprot into design mode to change the
sql, you can pass the sql, and use the rerpots on-laod event.


ig:

dim strSql as string
strSql = "select * from tblcustomers where City = 'Edmonton' "

DoCmd.OpenReport "myReprots, acViewPreview, , , , strSql

In the reprots on-load event, go:

If IsNull(Me.OpenArgs) = False Then
Me.RecordSource = Me.OpenArgs
End If

However, even BETTER is to NOT change/play/modify the reports reocrdsouce,
and the feature in ms-access designed for this. That feature is the "where"
clause, and is provided to avoid the mess you have now....

Simply go:

dim strWhere as string

strWhere = "City = 'edmonton'"
DoCmd.OpenReport "myReprots, acViewPreview, ,strWhere

With the above example:

We did not have to change the reocrdsouce of the report
We did not have to add code, or using the openargs of the report
We have a MUCH better report design mode, since the report can remain
bound, and we do not mess/play with/change the reports reocrdsouce.

Any design that tries to modify, or change forms/reports into design mode at
runtime is simply un-workable, and will also as a general rule COMPLETE
TRASH ANY stability you have. Flipping into design mode causes your
application to be come un-compiled -- this results in slow load times,
bloat, and even instability.

All of the above amounts of problems, and work can be eliminated by the
SIMPLE use of the where clause (that is what the feature is there for)

Here is some more ideas:

(a repost of mine)

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

 

[Guest]

Reports don't have a Load event.
Use the Open event.

Is this
considered a "clean" way of accomplishing this? Or is there a better way
to
reuse a report by feeding it different parameters?

Throwing the reprot into desing mode to accomplish this task is a VERY poor
desing decision.

Modifying reprots in design mode at runtime will cause EXCESSIVE BLOAT in
use of the aplcations.

Worse, is that you can't have occaosonal multi-user access, since hte reprot
is being modifed by code. However, for any type of multi-user setup, you
have to split the database anyway.

First, you don't need to throw teh reprot into design mode to change the
sql, you can pass the sql, and use the rerpots on-laod event.


ig:

dim strSql as string
strSql = "select * from tblcustomers where City = 'Edmonton' "

DoCmd.OpenReport "myReprots, acViewPreview, , , , strSql

In the reprots on-load event, go:

If IsNull(Me.OpenArgs) = False Then
Me.RecordSource = Me.OpenArgs
End If

However, even BETTER is to NOT change/play/modify the reports reocrdsouce,
and the feature in ms-access designed for this. That feature is the "where"
clause, and is provided to avoid the mess you have now....

Simply go:

dim strWhere as string

strWhere = "City = 'edmonton'"
DoCmd.OpenReport "myReprots, acViewPreview, ,strWhere

With the above example:

We did not have to change the reocrdsouce of the report
We did not have to add code, or using the openargs of the report
We have a MUCH better report design mode, since the report can remain
bound, and we do not mess/play with/change the reports reocrdsouce.

Any design that tries to modify, or change forms/reports into design mode at
runtime is simply un-workable, and will also as a general rule COMPLETE
TRASH ANY stability you have. Flipping into design mode causes your
application to be come un-compiled -- this results in slow load times,
bloat, and even instability.

All of the above amounts of problems, and work can be eliminated by the
SIMPLE use of the where clause (that is what the feature is there for)

Here is some more ideas:

(a repost of mine)

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

 

[David W. Fenton]

Without using the AutoCompact feature this will bloat your
database.

Use a query as record source. The query can be modifed in a MDB:

CurrentDb.QueryDefs.Item("ReportQuery").SQL = sSQL

That will cause bloat, too.

The only answer that doesn't cause bloat or involve dependencies in
saved queries is to set the report's recordsource in the report's
OnOpen event. This is the only method I ever use for filtering
reports that is more complicated than can be accomplished by passing
a filter in the OpenReport command.