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(rptName 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("qryName", strSQL)
Me.RecordSource = "qryName"

Public Sub UpdateQuery(qName 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(qName)
qItem.SQL = qSQL
qItem.Close
db.QueryDefs.Refresh

set qItem = Nothing
set db = Nothing

End Sub


.

Relevant Pages

  • RE: Query form coding
    ... I have tried to include the query when using the wizard to design the report ... Dim strSource As String ... ' Remove Filter ...
    (microsoft.public.access.formscoding)
  • RE: Need help with Code Please!!!
    ... MsgBox "The qryCompany\USFNumber query returned no records?", ... vbCritical, "NOTHING TO REPORT" ... Dim qd As QueryDef 'object ref to query item ... Dim myDataSource As String ...
    (microsoft.public.access.formscoding)
  • Re: Passing Form input to stored procedure to populate a report
    ... opens the form or report and pass them to an array vaiable, ... Public astrParamsAs String ... Dim stDocName As String ... The form/report opens ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Passing Form input to stored procedure to populate a report
    ... opens the form or report and pass them to an array vaiable, ... Public astrParamsAs String ... Dim stDocName As String ... The form/report opens ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Use wildcard in combo box with multiple strings
    ... Add the field to the query then. ... filter by it, it has to be in the query... ... use the field to filter what rows are in the RecordSource for the report. ... The string you use as a filter must repeat the name of the field for ...
    (microsoft.public.access.forms)