Close report and save changes without prompt

[jeffreybrown]

I have a report which is opened through a cmd button with code on the On Click Event

 Private Sub cmdMentor_Click()
    Dim MentorSQl As String
    MentorSQl = "select * from Force_Support_T where [Mentor Name] Is Null and [Centrally Managed Posn Type Desc]='Pal Acq (PAQ) Int Posn'"
    DoCmd.OpenReport "rptMentorNull", acDesign
    Reports!rptMentorNull.RecordSource = MentorSQl
    DoCmd.OpenReport "rptMentorNull", acViewReport
End Sub

Everything opens fine, but when I close the report it asks to save changes which none have been made by the user. I guess the fact the "Record Source" is getting updated makes the report recognize a change has taken place.

I tried placing

Private Sub Report_Close()
    DoCmd.Close acReport, "rptMentorNull", acSaveYes
End Sub

on the On Close Event of the report, but I still receive the "Do you want to save changes" dialogue box and when I click you the code debug to the DoCmd.Close code above.

What I am missing here?

Edit: I'm just closing the Report with the top right 'X'

 

[pbaldy]

I would do it differently, in part because opening in design view will fail if your users have the runtime version of Access (which many/most of us give to users). There are a few ways to go, but closest to what you're doing may be to pass the SQL in the OpenArgs argument of OpenReport. You'd delete these 2 lines here:

DoCmd.OpenReport "rptMentorNull", acDesign
Reports!rptMentorNull.RecordSource = MentorSQl

add the variable to the OpenArgs argument of OpenReport and then have this in the open event of the report:

Me.RecordSource = Me.OpenArgs

 

[jeffreybrown]

Thank you Paul. This worked very nice.

 

[pbaldy]

Happy to help!

 

[jeffreybrown]

Hi Paul,

I just noticed using the cmd buttons the report open opens fine using the OpenArgs argument of OpenReport; however, if the user tries to run the report simply by double clicking the report in the Navigation Pane then a Run-time error '94': Invalid use of Null is raised.

On clicking debug it points to...

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

Is there anyway this needs to be configured to avoid this error?

 

[pbaldy]

If Not IsNull(Me.OpenArgs) Then
  Me.RecordSource = Me.OpenArgs
End If

 

[jeffreybrown]

Works like a champ. Thanks again.

 

[pbaldy]

No problemo!