the report has to be open to change the recordsource. Here is some code
to do that
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SetRecordSource(_
ByVal pReportName As String, _
ByVal pSQL As String)
' written by Crystal
' strive4peace2006 at yahoo.com
' PARAMETERS:
' pReportName is the name of your report
' pSQL is an SQL string or tablename or queryname
' USEAGE:
' SetRecordSource "MyReportname","QueryName"
' SetRecordSource "MyAppointments", _
"SELECT * FROM Addresses WHERE City='Denver';"
On Error GoTo err_proc
Dim rpt As Report
'you can remove these lines once everything works ok
debug.print pReportName & " --> "
debug.print pSQL
'open as hidden if you don't want to see what is happening
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.RecordSource = pSQL
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
'if you want to open report now, remove comment
'DoCmd.OpenReport pReportName, acViewPreview
'if you want to use the WHERE parameter
'to further filter the report
'DoCmd.OpenReport pReportName, _
acPreview, , "condition"
Proc_exit:
Exit Sub
err_proc:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetRecordSource"
'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume
Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~
optionally, you can make the report be based on a query and change the
query before you open the report...
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)
'crystal
'(e-mail address removed)
'modified 6-29-06
On Error GoTo Proc_Err
'if query already exists, update the SQL
'if not, create the query
If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & strQueryName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If
Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub
Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"
'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume
Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~
and yet another thing you can do is use the Report Open event to change
the RecordSource
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
dbornt wrote:
Help! How do I change the record source of a report? I see the example of
changing record source for form:
forms!myform.recordsource = qryMyquery
When I try to do the same with report:
Reports!myreportname.RecordSource = mySqlStatement
It gives me an error message saying the report is spelled incorrectly or
report is not open. What am I doing wrong?
happy to help