Set Reports Record Source with Vb

[wizcow2]

I want to open a report from a forms command button.
I also want to set the reports record source as it opens.

This code generates an error
'You can't set recordsource after printing starts'

DoCmd.OpenReport "DailyCashOutRpt", acPreview
Reports!DailyCashOutRpt.RecordSource = qryArchivedDailyCashOut

Is this even possible?

Tom

 

[Nouba]

You can set an appropriate RecordSource for the report in its Open Event.

 

[wizcow2]

Thanks Nouba.

Right now I have four different reports with four different querys as their record source.

The reports are exactly the same.

If I could change the record source on the fly, I would need only one report.

Is there a way to change the OnOpen event on the fly from a form?

Tom

 

[Nouba]

There might be different ways to set the right query for your report. You can use a public variable in a global modul or lookup at a control value in the report's open event. Some Access-Version (AXP, A2K?) have the OpenArgs argument in the OpenReport method.

By using a global variable you should set its value to the name of the query from the form before you open the report.

' in a global module
Public gstrQueryToUse As String

' in the calling form module
' assuming the possible selections are in a combobox
gstrQueryToUse = Me!MyCombobox
DoCmd.OpenReport "ReportName", acViewPreview

' in the report module
Private Sub Report_Open(Cancel As Integer)
  Me.RecordSource = gstrQueryToUse
End Sub

If one knew by what the queries differ, a change of the report criteria could lead also to the goal.

 

[wizcow2]

Norbert

Your a genius! I hadn't thought of that.

Thanks
Tom