change report using VBA, then view and close it

[Philip Leduc]

this is the simplified code to: win XP, access 2003, 2000 file format
everything works fine, except when you close the report after viewing it
(using the close button on the toolbar) it opens in design view, if you use
the close window on the top right it works fine.
Why this different behaviour and can it be changed without juggling with the
toolbars?

'open report in design view
Dim stDocName As String
stDocName = "rptX"
DoCmd.OpenReport stDocName, acViewDesign

'change record source
strSQL1 = "Select * from tblX where [Y] =1"
Reports!RptX.Report.RecordSource = strSQL1

'save report
DoCmd.Save acReport, stDocName
'preview report
DoCmd.OpenReport stDocName, acViewPreview

 

[Albert D. Kallal]

You REALLY REALLY REALLY REALLY do not want to change forms during runtime
of a application.

First, you will find your applications just bloats (increases) in size like
no tomorrow. Further, if any code in the report, then saving it means your
appcation can become un-compiled. (that means even more in-stability, and
worse you can't distribute a mde file to your users. And, you REALLY should
be distributing a mde).

So, two tings:

First, why not set the sql of the reportt to a query, or a table.

you then to "change" the paramters simply use a where clause

eg:

dim strWhere as string
Dim stDocName As String
stDocName = "rptX"

strWhere = "where Y = 1"

docmd.Openreprot stdocname,acViewPreiview,,strWhere

Also, if you *must* change the sql, and you are not changing the conditions,
then you can do this in the reports on-open

dim strSql as string

strSql = "Select * from tblX where Y =1"

DoCmd.OpenReport stDocName, acViewPreview, , , , strSql

In the reports on-open event, go


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


The open arges is only available in a2002 and later. For previous versions
of ms-access, you can still set the recordsouce of the form in the on-open
event, but you have to set a global var in place of openargs.

And, if you just need to change the condtions, then use the "where" clause
of the openreprot..that is what it is desinged for..

 

[Jerry Whittle]

What Albert said - of course! If you ever convert the database to an MDE,
you'll be in for an unpleasent surprize when you try to run the code.

If you still want to do this, what event(s) have this code? You might not be
choosing the right event.

 

[Philip Leduc]

Hi,

The technique is simplified from the Access cookbook v2 from Ken Getz.
chapter 3-15
The problem even occurs on the sample mdb as on the CD that comes with the
book...
Also as a developer for small businesses, I seldomly use mde files
I guess I will have to make my own toolbar and show and remove it.

Philip

 

[Larry Daugherty]

Not all of us have that book. [Your "v2" sent me back to my Access
Version 2.0 books. There is a related "Cookbook" for that version but
not by Getz. Then I checked Barnes & Noble and got a hit on the one I
think you mean

The authors had a purpose and a context for their application. What
are *your* purpose and *your* context for *your* issue?

HTH
--
-Larry-
--

Hi,

The technique is simplified from the Access cookbook v2 from Ken Getz.
chapter 3-15
The problem even occurs on the sample mdb as on the CD that comes with the
book...
Also as a developer for small businesses, I seldomly use mde files
I guess I will have to make my own toolbar and show and remove it.

Philip

this is the simplified code to: win XP, access 2003, 2000 file
format
everything works fine, except when you close the report after viewing it
(using the close button on the toolbar) it opens in design view, if you
use the close window on the top right it works fine.
Why this different behaviour and can it be changed without juggling with
the toolbars?

'open report in design view
Dim stDocName As String
stDocName = "rptX"
DoCmd.OpenReport stDocName, acViewDesign

'change record source
strSQL1 = "Select * from tblX where [Y] =1"
Reports!RptX.Report.RecordSource = strSQL1

'save report
DoCmd.Save acReport, stDocName
'preview report
DoCmd.OpenReport stDocName, acViewPreview

 

[Philip Leduc]

Hi Larry,

Sorry about the confusion. I do not think the purpose matters though it is
no secret. (I found a workaround by throwing in another toolbar without the
close button but would like to learn some understanding why this happens)

I use a form with comboboxes and text boxes to build a select statement that
filters a subform in datasheet view through its recordsource (call it a
controlled interface for a query) . Also reports with the same record source
is connected so that it filters the same way. I usually just replace then
the report recordsource in the open report event (equal to the subform) but
in some occasions it would be convenient to do this from the form itself (
reuse of code for example or different recordsource wiht related data such
as all the contacts for the projects listed in my subform).

The questions stays why using the close button on the toolbar opens it in
design view and x-ing it out does not?

Philip

Not all of us have that book. [Your "v2" sent me back to my Access
Version 2.0 books. There is a related "Cookbook" for that version but
not by Getz. Then I checked Barnes & Noble and got a hit on the one I
think you mean

The authors had a purpose and a context for their application. What
are *your* purpose and *your* context for *your* issue?

HTH
--
-Larry-
--

Hi,

The technique is simplified from the Access cookbook v2 from Ken Getz.
chapter 3-15
The problem even occurs on the sample mdb as on the CD that comes with the
book...
Also as a developer for small businesses, I seldomly use mde files
I guess I will have to make my own toolbar and show and remove it.

Philip

this is the simplified code to: win XP, access 2003, 2000 file
format
everything works fine, except when you close the report after viewing it
(using the close button on the toolbar) it opens in design view, if you
use the close window on the top right it works fine.
Why this different behaviour and can it be changed without juggling with
the toolbars?

'open report in design view
Dim stDocName As String
stDocName = "rptX"
DoCmd.OpenReport stDocName, acViewDesign

'change record source
strSQL1 = "Select * from tblX where [Y] =1"
Reports!RptX.Report.RecordSource = strSQL1

'save report
DoCmd.Save acReport, stDocName
'preview report
DoCmd.OpenReport stDocName, acViewPreview

 

[Larry Daugherty]

Gotcha'. I don't know the answer. Rather than spend a lot of time
fighting it I've worked around similar instances with tactical
"Maximize" commands.

HTH
--
-Larry-
--

Hi Larry,

Sorry about the confusion. I do not think the purpose matters though it is
no secret. (I found a workaround by throwing in another toolbar without the
close button but would like to learn some understanding why this happens)

I use a form with comboboxes and text boxes to build a select statement that
filters a subform in datasheet view through its recordsource (call it a
controlled interface for a query) . Also reports with the same record source
is connected so that it filters the same way. I usually just replace then
the report recordsource in the open report event (equal to the subform) but
in some occasions it would be convenient to do this from the form itself (
reuse of code for example or different recordsource wiht related data such
as all the contacts for the projects listed in my subform).

The questions stays why using the close button on the toolbar opens it in
design view and x-ing it out does not?

Philip

Not all of us have that book. [Your "v2" sent me back to my Access
Version 2.0 books. There is a related "Cookbook" for that version but
not by Getz. Then I checked Barnes & Noble and got a hit on the one I
think you mean

The authors had a purpose and a context for their application. What
are *your* purpose and *your* context for *your* issue?

HTH
--
-Larry-
--

Hi,

The technique is simplified from the Access cookbook v2 from Ken Getz.
chapter 3-15
The problem even occurs on the sample mdb as on the CD that comes with the
book...
Also as a developer for small businesses, I seldomly use mde files
I guess I will have to make my own toolbar and show and remove it.

Philip

this is the simplified code to: win XP, access 2003,

2000
file

format
everything works fine, except when you close the report after viewing it
(using the close button on the toolbar) it opens in design

view,
if you

use the close window on the top right it works fine.
Why this different behaviour and can it be changed without juggling with
the toolbars?

'open report in design view
Dim stDocName As String
stDocName = "rptX"
DoCmd.OpenReport stDocName, acViewDesign

'change record source
strSQL1 = "Select * from tblX where [Y] =1"
Reports!RptX.Report.RecordSource = strSQL1

'save report
DoCmd.Save acReport, stDocName
'preview report
DoCmd.OpenReport stDocName, acViewPreview