No prompt to save changes to report

[Adrian]

Using Access 2000 and SQL Server 2000

I have inherited a database (.adp) that sets the input parameters for
reports at run-time.

It then saves the report before opening it to display to the user. The input
parameters are passed to a stored procedure that is the data source of the
report.

This is a sample code:

DoCmd.OpenReport strRpt, acViewDesign
Reports(strRpt).InputParameters = strParams
DoCmd.Close acReport, strRpt, acSaveYes
DoCmd.OpenReport strRpt, lngReportAction

If the report is not saved first it runs much more quickly, but when the
user closes the report they are prompted if they want to save the changes.

Is there any way of stopping the users being prompted about the changes? Or
any other suggestions?

Thanks for any help.

Adrian

 

[Baisong Wei[MSFT]]

Hi Adrian,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

For my understanding of your question, you have a report of the results of
a stored procedure with parameter. When you call it and close it, then the
system will prompt a save dialog to you, asking you to save the changes,
and you do not want this dialog to appear, right? If I misunderstood,
please feel free to let me know.

Well, I do a test as follows, but did not see the save dialog, could you
check my steps and see if there is any difference from yours?
1) Create a stored procedure on SQL Server 2000. In Query Analyzer in SQL
Server, run

use pubs
go
create proc author_info
@lname varchar(40)
as
select * from authors where au_lname=@lname
go
exec author_info 'Green'

The result is correct.

2) Open a new project, connect to my SQL Server. Then in the Queries, you
will find author_info.
3) Create a report, and the data is from 'author_info'. Save it as report
'author_info'. Double click the report 'author_info', a dialog will prompt
asking you to enter parameter value. Then type in 'Green' and you will get
the preview of the report, in which have record of last name is 'Green'

4) Create a form with a button, for the Onclick event:

Private Sub Command0_Click()
DoCmd.OpenReport "author_info", acViewDesign
Reports(author_info).InputParameters = strParams
DoCmd.Close acReport, "author_info", acSaveYes
DoCmd.OpenReport "author_info", lngReportAction
End Sub

Then save this form as Form1.

You could then close all the Access windows and reopen it. When open Form1
and click the button, a dialog will prompt, asking you to enter parameter,
type the 'Green', you will get the report of the authors with last name is
'Green' printed. I did not encouter the dialog asking me to save the
changes..
Could you try these steps and check if you encounter that again. I am
looking forward to you response.

Best regards

Baisong Wei
Microsoft Online Support

 

[Adrian]

Baisong,

Thanks very much for your detailed reply.

and you do not want this dialog to appear, right?
Correct.

I did not encouter the dialog asking me to save the changes..

There are a couple of things you need to change in the code.

Firstly you are not passing the parameters to the report (if you were you
would not get the dialog box asking you to enter the parameter).
Secondly, you need to rem out the line 'DoCmd.Close acReport, "author_info",
acSaveYes' to get the save prompt.
The following code should work:

Private Sub Command0_Click()
Dim strParams As String

strParams = "@lname varchar = 'Green'"

DoCmd.OpenReport "author_info", acViewDesign
Reports("author_info").InputParameters = strParams
'DoCmd.Close acReport, "author_info", acSaveYes
DoCmd.OpenReport "author_info", acViewPreview
End Sub

Having done a little more testing I have realised there is slightly more to
this than I first thought.

If I run your test, saving the report using 'DoCmd.Close acReport,
"author_info", acSaveYes' takes negligible time, and therefore there is no
problem leaving this code in (and therefore I do not get a prompt to save
the report).

However, our application is a large application and all reports etc are run
from a main 'switchboard' form. This is a complex, multi-tabbed form (we use
sourcesafe and the size of the ACF file is 249KB) .

Any report that has no code in it (no module) will open almost
instantaneously, even leaving in the line 'DoCmd.Close acReport,
"author_info", acSaveYes' .

But as soon as we put any code in a report it takes around 10 seconds for
the report to save if we leave in the line 'DoCmd.Close acReport,
"author_info", acSaveYes'. This seems to be because not only is it saving
the report, but also the switchboard form. I do not understand why!

I could probably remove some of the code behind the switchboard form, but I
suspect it is still going to be quite a large form and therefore there will
still be a considerable delay in opening reports.

I appreciate I could also avoid seeing the save prompt if I do not run the
line 'DoCmd.Close acReport, "author_info", acSaveYes' by setting warnings
to false before opening the report, and back to true on the close event of
the report, but I am uneasy with this.

Sorry for such a long post, and hope you can understand my garbled
explanation.

Adrian

 

[Baisong Wei[MSFT]]

Hi Adrian,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

You detailed information is much appreciated for our analysis and helpful
in solving your problem. As you mentioned that when put code in a report, I
suppose this means using the following code:

Dim strParams As String
strParams = "@lname varchar = 'Green'"

to create a report and then save this report by 'DoCmd.Close acReport,
"author_info", acSaveYes'. It will last 10 seconds and you mentioned that
this is a 'large application'. From my experience, when you are saving some
design change to modules such as Form, Report and standard modules in a
large database, it will take more time because of the changes in the
project storage model in Microsoft Access 2000 required by the Visual Basic
Environment.

Please refer this part in the following article:
ACC2000: Saving Objects in Large Database Slower Than in Earlier Versions
http://support.microsoft.com/?id=246306

As the code provided in the second post:

DoCmd.OpenReport "author_info", acViewDesign
Reports("author_info").InputParameters = strParams
'DoCmd.Close acReport, "author_info", acSaveYes
DoCmd.OpenReport "author_info", acViewPreview

You use ViewPreview instead of 'author_info', if you do not want to use set
warnings to avoid the saving dialog to prompt. You have to save the report
first. If you preview a new created report and want to avoid seeing the
saving dialog, the 2 way for you to do that is, save first or set warnnings
by

DoCmd.SetWarnings(WarningsOff)

Hope this helpful to your question. If you still have questions, please
feel free to post new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support

 

[Adrian]

Baisong,

Thanks for your help.

I guess I have no option other than to use DoCmd.SetWarnings(WarningsOff),
unless I want to live with the 10 second delay in opening reports. I will
have to do some reading as to exactly what warnings this will affect.

Adrian.

 

[Adrian]

Baisong,

I spoke too soon!

If I set the warnings to off I do not get the delay in opening the report,
but it saves it when it is closed so the long delay is in closing the
report!

I tried setting the Input Parameters using the Open_Report event as I
believe changes made in that way are automatically temporary and no attempt
is made to save them. However I cannot get this to work. Using your original
example report of 'author_info' I am trying:

Private Sub Report_Open(Cancel As Integer)
Me.InputParameters = "@lname varchar = 'Green'"
End Sub

Any ideas?

Thanks again for your help.

Adrian.

 

[Lyle Fairfield]

I tried setting the Input Parameters using the Open_Report event as I
believe changes made in that way are automatically temporary and no
attempt is made to save them. However I cannot get this to work. Using
your original example report of 'author_info' I am trying:

Private Sub Report_Open(Cancel As Integer)
Me.InputParameters = "@lname varchar = 'Green'"
End Sub

In a standard module create a function. Code that function to return the
string:

"@lname varchar = 'Green'"

in your report design set the value of the input parameters to:
=YourFunctionName().

 

[Adrian]

Lyle,

Thanks for your advice.

I created a function:

Public Function TestFunction() As String
TestFunction = "@lname varchar = 'Green'"
End Function

and set the value of the Report Input Parameters to: =TestFunction() (and
also tried without the equals sign).

However, although it did not prompt me for the parameters, it did not
display the Author with the name 'Green' (I did check that it worked if I
typed in the parameter manually).

Am I doing something wrong here, or . . . .

I could post the adp I am testing this with, it is only 30KB.

Adrian

 

[Lyle Fairfield]

Lyle,

Thanks for your advice.

I created a function:

Public Function TestFunction() As String
TestFunction = "@lname varchar = 'Green'"
End Function

and set the value of the Report Input Parameters to: =TestFunction() (and
also tried without the equals sign).

However, although it did not prompt me for the parameters, it did not
display the Author with the name 'Green' (I did check that it worked if I
typed in the parameter manually).

Am I doing something wrong here, or . . . .

I could post the adp I am testing this with, it is only 30KB.

Adrian

I'm sorry. I realize now that my function would return only:
"Green"
..

 

[Adrian]

I'm sorry. I realize now that my function would return only:
"Green"
.


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Lyle,

I'm sorry. I realize now that my function would return only:
"Green"

I am not quite sure what you mean by this as the function does return the
value "@lname varchar = 'Green'", and it is definitely being called on
opening the report.

But maybe it is just one of those thigs that do not work.

Adrian.

 

[Lyle Fairfield]

I am not quite sure what you mean by this as the function does return
the value "@lname varchar = 'Green'", and it is definitely being called
on opening the report.

But maybe it is just one of those thigs that do not work.

Adrian.

I mean the function does not return
"@lname varchar = 'Green'"
but returns
"Green"

 

[Lyle Fairfield]

I mean the function does not return
"@lname varchar = 'Green'"
but returns
"Green"

upon even further review I realize that the input parameters property
should read:
@lname varchar=TestFunction(),@parm2 int=TestFunction2() ....

and TestFunction
should read:
Public Function TestFunction() As String
TestFunction = "Green"
End Function
Public Function TestFunction() As Long
TestFunction = 7
End Function

 

[Lyle Fairfield]

upon even further review I realize that the input parameters property
should read:
@lname varchar=TestFunction(),@parm2 int=TestFunction2() ....

and TestFunction
should read:
Public Function TestFunction() As String
TestFunction = "Green"
End Function
Public Function TestFunction() As Long
TestFunction = 7
End Function

arghhh ... of course the last should be
Public Function TestFunction2() As Long
TestFunction2 = 7
End Function

 

[Adrian]

arghhh ... of course the last should be
Public Function TestFunction2() As Long
TestFunction2 = 7
End Function


--


Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Lyle,

Thank you very much.

It works very well and from a quick test simple reports are now almost
instantaneous to open and close.

It will make a tremendous difference to our database, so again many thanks.

Adrian