Record source change through VBA

[Young H. Lee]

Based on the record selection criteria entered by a user
(by city, by community, by zip code, or for all), a set of
reports have to be generated. Each report has many
supreports. One solution is to create seperate queries
matching to the various record selection criteria, and
create reports/subreports having those queries as record
sources.

Problem with this approach is the proliferation of queries
and reports/subreports that would increase maintenance
costs.

Please show me how to change the record sources of reports
and subreports dynamically, i.e. through an event
procedure activated upon click a button in the record
selection criteria entry form.

Queries are the tools to generate SQL statements, and I
know that the latter can be generated through a VBA. So
the question boils down to the following:

(1) Is it possible to change the record source of a report
dynamically?
(2) If so, how?

Thank you for your attention.

 

[Guest]

You can change the recordsource of a report in the Open event. You could
load public string variables associated with your various
reports/sub-reports with sql strings before opening and have the Open event.
You may have some problems with Master/Child linking though. You'd have to
test.

Another technique is to store one query for each report (or sub-report) in
your database. Base your report on the stored querydef, then update the
querydefs before you open the reports.

Steve

 

[Young H. Lee]

Hi Steve,

Thank you for your prompt reply. I want to follow your
second recommendation. How can I update the querydef for a
report before I open it?

If only I can do so, it will reduce the number of queries
and reports/subreports I have to create by reusing the
basic ones already developed. Thanks again for your help.

Young

 

[Guest]

A sample function could be this:

Sub NewQuery(strQName As String, strSql As String)
'Purpose: to write out a query for use by a report or code
'Parameters: strSql - query string, strQName - name for query

'need a querydef object
Dim z_qdf As QueryDef

'getting ready for an error erasing querydef if it doesn't exist
On Error GoTo ProcError

'Delete if existing query trap err 3011
If QueryExists(strQName) = True Then
'function below
DoCmd.DeleteObject acQuery, strQName
End If

'save new/revised query to database window
Set z_qdf = CurrentDb.CreateQueryDef(strQName, strSql)

'refresh the database window
Application.RefreshDatabaseWindow

ProcExit:
Set z_qdf = Nothing
Exit Sub

ProcError:

Select Case Err.Number
Case 3011 'The Microsoft Jet database engine could not find the
object
Resume Next
Case 7874 'Compass can't find the object 'a_qryTemp.'
Resume Next
Case Else
MsgBox "Unanticipated Error " & Err.Number & " " & Err.Description
'Stop
Resume ProcExit
End Select
End Sub

Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function


In some code, perhaps in a click event on a form,

NewQuery "qryReport_Labels", "SELECT * FROM tblSales WHERE CustomerID = " &
Me.cboCustomer
DoCmd.OpenReport "Report_Labels", acViewPreview

If the report has a sub-report:

NewQuery "qryReport_Labels", "SELECT * FROM tblSales WHERE CustomerID = " &
Me.cboCustomer
NewQuery "qryReport_LabelsSub", "SELECT * FROM tblInvoice WHERE CustomerID =
" & Me.cboCustomer
DoCmd.OpenReport "Report_Labels", acViewPreview

Steve