Changing Report DataSource based on Opening source in VBA

[txgeekgirl]

I have a report that has a data source of the query "Activity Feeder Query". Week is defined as [Forms]![User Entry Form Tabbed]![Week].

That worked great until I needed to create an administrative interface where the report would be called outside of the user interface.

I have duplicated the query and named it "Alt Activity Feeder Query" and defined Week to be [Forms]![Report Retriever]![Week].

How can I, in VBA, either On Activate or On Open make it change the data source.

If CurrentProject.AllForms("User Entry Form Tabbed").IsLoaded Then
DoCmd.
ElseIf CurrentProject.AllForms("Report Retriever").IsLoaded Then
DoCmd.
End If

 

[pbaldy]

I would probably have gone a different direction, but rather than DoCmd you'd use

Me.RecordSource = ...

in the report's open event. In case you're wondering, I would have done this so I only had one query, which would not have the criteria in it:

http://www.baldyweb.com/wherecondition.htm

 

[gemma-the-husky]

are you calling the report by clicking a button on a form, or from a menu

if the former, then just pass the name of the form as an openarg.

someimes it's even easier to have two separate reports, than try to squeeze extra functionality - although in this case, i'm sure it makes sense

 

[txgeekgirl]

Here's what I have so far:

From the Call to open the report.

If Prepare_Report_Format(Me.User_Echo) = True Then
DoCmd.OpenReport "Activities", acViewPreview, "Alt Activity Feeder Query"
DoCmd.OpenReport "TimeStudy", acViewPreview
[Forms]![MacCheckList].imBuildTime.Visible = True

The Open for the Report

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Nz(Me.OpenArgs, "Alt Activity Feeder Query")
End Sub


I have to have the Week criteria because the report auto feeds dates based on it.

I still have errors, but they are native to the query.

 

[txgeekgirl]

Thank you all for your help. I was able to use code to change the source depending on which form was trying to build the report .