Dynamically Changing Reports

[scadav]

Hi,

I am allowing a user to chose multiple criteria from a form in order to
create a custom report. Once they have chosen all the criteria I build
a SQL statement and put that statement in the record source of the
report, then generate a print preview of the report report for them to
view. I do this in the following manner:

DoCmd.OpenReport ("SearchResults"), acViewDesign
Reports![SearchResults].Report.RecordSource = sSQLCommand
DoCmd.OpenReport ("SearchResults"), acViewPreview
DoCmd.Save acReport, "SearchResults"

My problem arises from the last line "DoCmd.Save acReport,
"SearchResults"". I put this in place because it kept prompting me to
save changes when I closed the print preview of the report and I didn't
want the users to see this pop-up. This line works fine as long as no
one else is in the database. Once I put this into production (in a
multi-user environment), that line item causes a run-time error saying
that it can't obtain exclusive rights.....

Since these reports are dynamic, I don't really care if they get saved,
so does anyone know how to prevent the save change pop-up from coming
up or how to automatically lose the changes on close?

Thanks for you help.

 

[Brendan Reynolds]

You can change the RecordSource property of the report in the Report_Open
event procedure without opening the report in design view. When you close
the report, the change will be discarded without prompting. The code would
look something like ...

If CurrentProject.AllForms("YourFormName").IsLoaded Then
Me.RecordSource = Forms("YourFormName").SqlStatement
End If

In the above example, "SqlStatement" could be a text box on the form (you
can set the Visible property to False to hide it if you want) or it could be
a public variable or property of the form. Just assign your SQL string to
this text box, variable or property before opening the report.

That said, though, it sounds as though you are sharing the 'front-end'
application MDB, and that is not recommended. A better option is to split
the 'front-end' application MDB from the 'back-end' data MDB. The data MDB
contains nothing but tables, and lives on the server. The application MDB
contains everything else, including links to the tables in the data MDB, and
a copy of the application MDB goes on each client PC. Albert Kallal has an
article on this subject at the following URL ...

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[scadav]

Thakn you Bendan. I will give it a try.