Locking in Queries for Reports

[Donald Calloway]

I have written an application which includes 13 reports based on 39
individual queries. If a superuser happens to open any of these queries
and performs filtering of a dataset to satisfy a particular demand for
information outside of a report then re-saves the altered query by
accident, whenever a report on which that query is based is run the
results will not be as expected. How can a query itself be set as
read-only so that it cannot be modified by accident so this doesn't happen?

[DFS]

Re: Locking in Queries for Reports

Donald Calloway wrote:

I have written an application which includes 13 reports based on 39
individual queries. If a superuser happens to open any of these
queries and performs filtering of a dataset to satisfy a particular
demand for information outside of a report then re-saves the altered
query by accident, whenever a report on which that query is based is
run the results will not be as expected. How can a query itself be
set as read-only so that it cannot be modified by accident so this
doesn't happen?

To ensure this never happens, you can set the RecordSource directly during
the report Open event:


1) Me.RecordSource = "SELECT blah blah blah..."


2) You could centralize all your report recordsources in a function and call
the function when the report opens:

Public Function getReportData(r ptName As String) as String

if rptName = "thisReport " then
getReportData = "SELECT blah blah...
elseif rptName = "thatReport " then
getReportData = "SELECT blah blah...
end if

End Function

Me.RecordSource = getReportData(" thisReport")
or
Me.RecordSource = getReportData(" thatReport")

Note that 'thisReport' and 'thatReport' are Strings, and are not references
to the Report object itself.


3) If you need the query for uses besides the report, you could update the
SQL statement each time the report opens:

dim strSQL as String
strSQL = "SELECT blah blah.."
Call updateQuery("qr yName", strSQL)
Me.RecordSource = "qryName"

Public Sub UpdateQuery(qNa me As String, qSQL As String)

'UPDATES THE SQL STATEMENT OF A QUERYDEF
dim qItem as QueryDef, db as Database
set db = currentdb()

Set qItem = db.QueryDefs(qN ame)
qItem.SQL = qSQL
qItem.Close
db.QueryDefs.Re fresh

set qItem = Nothing
set db = Nothing

End Sub