docmd.close, acsave not working

Wouldn't you want to use

acSaveNo

?

No because I'm opening the report hidden design view changing the record source, saving it, and re-opening it.  

I have 1 report that can have 2 different queries.  I'm trying to avoid creating 2 reports.

JOe K.

You can just set the Record Source on the fly ... no need to open in hidden design view.  No need to worry about saving.  Make the record source property blank, then set on the fly.  I do it all the time.

mx

won't it ask you to save when you close it?

Nope.

Try it.  Again, I've been doing that for years ... same report, on-the-fly setting of record source.

mx

I tried with the open report before and after the if statement.  It either says it's "open and cannot be edited in preview mode", or it says "it's missing or not open"

DoCmd.OpenReport "rptYearlyWPPI_Expenditures", acViewPreview
                
                If IsNull([Forms]![JobsStatsInfoForm]![MonthIndicator]) Then
                    [Reports]![rptYearlyWPPI_Expenditures]![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"
                    [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding_Null"
                Else
                    [Reports]![rptYearlyWPPI_Expenditures]![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_sbrpt"
                    [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding"
                    
                End If

Select allOpen in new window

Move the code that sets the record source to the OnOpen event of the Report.

mx

it worked.  How can I edit the subreports record source

[Reports]![rptYearlyWPPI_Expenditures]![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"

invalid reference for the subreport line of code.  The report works just not the subreport.  How would I reference it?

Is this

[qryWPPI_Funding subreport]

the name of the Sub Rerport *Control* ... as opposed to the name of the actual subreport?  You Control name s/b different than the Source Object, say something like rsub01 or what ever.

Then

[Reports]![rptYearlyWPPI_Expenditures].rsub01.Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"

basically, your syntax is correct.  Reports.ReportName.SubReportControlName.Report.SomeControlorProperty.

See this link for the ultimate reference in form/subform ... report/subreport systax

http://www.mvps.org/access/forms/frm0031.htm

mx

Me![qryWPPI_Funding SubReport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"

I'm trying to copy on main form refering to sub1

Me!Subform1.Form.RecordSource

Sorry, not following ... reports or forms?

"How can I edit the subreports record source"

??

mx

Sorry about the lack of clarity,

Reports.  The link you posted showed examples in terms of forms.  So I figured that you could just replace the form with report.  I posted the thing saying forms, to show you were I was getting the example from on that link.

"So I figured that you could just replace the form with report."

You can.

ok ... at this point, what is not working ?

mx

Using this code I get a "you have entered an expression that has an invalid reference to the property of a report/form":

     If IsNull([Forms]![JobsStatsInfoForm]![MonthIndicator]) Then
       
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding_Null"
        Me![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"
    Else
    .....

just to make sure I'm on the right track:
the source object is: Report.sbrptYearlyWPPI_Expenditures
the name is:  qryWPPI_Funding subreport

Thanks for your time and patience.

Private Sub Report_Open(Cancel As Integer)
    If IsNull([Forms]![JobsStatsInfoForm]![MonthIndicator]) Then
        
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding_Null"
        Me![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"
    Else
        [Reports]![rptYearlyWPPI_Expenditures]![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_sbrpt"
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding"
        
    End If
End Sub

Select allOpen in new window

Re this:

     If IsNull([Forms]![JobsStatsInfoForm]![MonthIndicator]) Then
       
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding_Null"
        Me![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"
    Else

Which like is the error occurring on ?

And qryWPPI_Funding subreport   is the Name of your Sub Report *control* ??

mx

The error is happening on this line:

this line is highlighted:  Me![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"

Where is that code?  In the Report On Open or Form or where ?

mx

report onOpen()

Private Sub Report_Open(Cancel As Integer)
    If IsNull([Forms]![JobsStatsInfoForm]![MonthIndicator]) Then
        
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding_Null"
        Me![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_Null_sbrpt"
    Else
        [Reports]![rptYearlyWPPI_Expenditures]![qryWPPI_Funding subreport].Report.RecordSource = "qryWPPI_Funding_sbrpt"
        [Reports]![rptYearlyWPPI_Expenditures].RecordSource = "qryWPPI_Funding"
        
    End If
End Sub

Select allOpen in new window

Could be a timing issue.

ok ... Put the same code - but for just the Subreport ... in the SubReport OnOpen ...  and leave the main report code in the OnOpen of main report ... if you see what I mean?

So ... the subreport will set it's on record source based on the IF/Else condition ... as will the Main report.

Modify the subreport syntax accordingly.

mx

it gets caught in an infinite loop where I have to ctrl break to get out of. It seems to keep formating the page.

I'm not at work anymore sorry about the late response.  

I think that is the code I should use.  

I overlooked using me. for the code.

thank you so much, I'll test it as soon as I stop by the office either tomorrow or monday.

Have a great weekend

ok

YOU ARE A GENIUS.  Thank you very much!!!

JOe K.

thank you for you time

you are welcome.

mx