Change the recordsource of a report

[hbrems]

Hello,

I want to change the recordsource of a report when I open it from a form. Something like this:

docmd.openreport "myreport"
myreport.recordsource = mysql
myreport.requery

The report is showing fine but the recordsource isn't adjusted.

Kind regards,
Hans B.

 

[Moniker]

After the requery, did you try a Refresh?

 

[hbrems]

After the requery, did you try a Refresh?

Don't pay too much attention to the code I provided. Hoped it would clarify what I'm looking for.

 

[allan57]

A recordset has a seldom-used property, Name, that gives you the table, query or SQL string that the recordset was opened with. And this is exactly what is needed to set a recordsource.

In the Open event of the report:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grst.Name
End Sub

In a standard module:

'
' This code was originally written by Andy Baron
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Andy Baron
'
Public grst As Recordset

Public Sub testreport()
Set grst = CurrentDb.OpenRecordset( _
"Select * from tblTest1 where longfield=4")
DoCmd.OpenReport "rptTest1", acViewPreview
grst.Close
Set grst = Nothing
End Sub