Setting RecordSource on open

[PJ]

I have a table that stores custom groups of people in SQL statements. Users
choose a group from a combo box. The idea is to then open a report with the
recordsource = the appropriate SQL.

The following works, but seems really clunky. There must be a better way.
Any help would be appreciated.

DoCmd.Echo False
DoCmd.OpenReport "rptTest",acViewDesign
Reports!rptTest.RecordSource = SQL
DoCmd.OpenReport "rptTest",acViewPreview
DoCmd.Echo True

 

[Squirrel]

Hi there,

Could the recordsource for the report be an existing query e.g.
qryReportSource?
Just reassign the SQL appropriately before opening the report.

To demonstrate the concept and see the result in the Immediate Window. You
would have
a function which accepts the SQL string as a parameter.

Public Function WriteQuery() as Boolean
CurrentDb.QueryDefs("qryReportSource").SQL = "Select LastName from
tblEmployees"
Debug.Print CurrentDb.QueryDefs("qryReportSource").SQL
End Function

?WriteQuery
SELECT LastName
FROM tblEmployees;

HTH -Linda

 

[PJ]

Thanks, I haven't tried it yet, but it is a much neater approach

 

[Dirk Goldgar]

I have a table that stores custom groups of people in SQL statements.
Users choose a group from a combo box. The idea is to then open a
report with the recordsource = the appropriate SQL.

The following works, but seems really clunky. There must be a better
way. Any help would be appreciated.

DoCmd.Echo False
DoCmd.OpenReport "rptTest",acViewDesign
Reports!rptTest.RecordSource = SQL
DoCmd.OpenReport "rptTest",acViewPreview
DoCmd.Echo True

In Access 2002 and later, DoCmd.OpenReport has an OpenArgs argument, so
you might pass the SQL string to the report like this:

DoCmd.OpenReport "rptTest", OpenArgs:=SQL

and then have code in the report's Open event that sets its RecordSource
from the OpenArgs, like this:

Private Sub Report_Open(Cancel As Integer)

If Len(Me.OpenArgs & vbNullString) > 0 Then
Me.RecordSource = SQL
End If

End Sub

If you're using an earlier version of Access, you might have the code in
the report's Open event pick up the SQL from the combo box on the form.

 

[PJ]

Thanks, I tried both suggestions, the openargs solution is the fastest.

 

[Bob McCormack]

I have several reports in an application that I want to open with different
recordsource's depending on various conditions.
I assign a public variable "RecSrc" and then have the base report open with
an OnOpen procedure that sets RecordSource = RecSrc.
The button/procedure that opens the report does so by first assigning RecSrc
equal to a query (eg RecSrc = "qryCondition1") and then running the
Docmd.OpenReport command.

Hope this makes sense.

Bob McCormack