Solved Use two buttons to print the same report where one prints the detail section ... Freezing my application (2 Viewers)

[DJL910]

Hi -

My goal is to present a user with two buttons - one that opens a report in print preview with the detail section visible and then one without it visible. I have built the On Click event for the two buttons and they open the report as required, toggling the visible true/false. However, once I close the report, I'm totally locked out of Access - meaning I can't click anywhere, even in the code window. I have to close down the Access application entirely. I assume that I'm not ending my code and am looking for some support on the proper syntax. Happy to provide any additional context and REALLY appreciate anyone's assistance here on what I hope is a straightforward issue.

Buttons
Button_PrintSummary
Button_PrintSummarywDetail

Code
Private Sub Button_PrintSummary_Click()
DoCmd.OpenReport "Rpt - SO by Customer", acViewPreview, "",, acViewPreview
Repots![Rpt - SO by Customer].Section(0).Visible = False
End Sub

> I chose AcDialog to set Modal and Popup to Yes automatically.

 

[DJL910]

Here is the corrected, updated code. I pasted a version I had been playing around with.

Private Sub Button_PrintSummary_Click()
DoCmd.OpenReport "Rpt - SO by Customer", acViewPreview, "", , acDialog
Reports![Rpt - SO by Customer].Section(0).Visible = False
End Sub

 

[DHookom]

I would use OpenArgs in the OpenReport to send “hide” or “show”. Then in the on open event, use VBA to set the detail section visible property.

 

[Isaac]

Do you have any additional custom code that 'hides the Access application/shell' ?

Just curious - when using that kind of thing in my past, it presented a big problem when using Access Reports.
Thankfully, I found very little use for Access Reports object - preferring usually to simply output 'reports' to Excel.

 

[DJL910]

I would use OpenArgs in the OpenReport to send “hide” or “show”. Then in the on open event, use VBA to set the detail section visible property.

Thanks for this suggestion; it makes sense to me. I figured I'd do "Level0" and "Level1" in the OpenArg and then do if statements in the Report on Open. However, I gave it a try and am now hitting up against a Run-time Error '2501': The OpenReport action was cancelled" on clicking the button, directing me to the line below in red. Any thoughts?

ON PRINT FORM
Code
Private Sub Button_PrintSummaryProduct_Click()
DoCmd.OpenReport "Rpt - SO by Product", acViewPreview, , , , "Level0"
End Sub

Private Sub Button_PrintSummaryProductDetail_Click()
DoCmd.OpenReport "Rpt - SO by Product", acViewPreview, , , , "Level1"
End Sub

ON REPORT
Code
Private Sub Report_Open(Cancel As Integer)
'Display no detail from Button_PrintSummaryProduct
If "" & OpenArgs = "" & "Level0" Then
Reports![Rpt - SO by Product].Section(0).Visible = False
End If

'Display detail from Button_PrintSummaryProductDetail
If "" & OpenArgs = "" & "Level1" Then
Reports![Rpt - SO by Product].Section(0).Visible = True
End If

End Sub

 

[azhar2006]

Here is the corrected, updated code. I pasted a version I had been playing around with.

Private Sub Button_PrintSummary_Click()
DoCmd.OpenReport "Rpt - SO by Customer", acViewPreview, "", , acDialog
Reports![Rpt - SO by Customer].Section(0).Visible = False
End Sub

Hi -

My goal is to present a user with two buttons - one that opens a report in print preview with the detail section visible and then one without it visible. I have built the On Click event for the two buttons and they open the report as required, toggling the visible true/false. However, once I close the report, I'm totally locked out of Access - meaning I can't click anywhere, even in the code window. I have to close down the Access application entirely. I assume that I'm not ending my code and am looking for some support on the proper syntax. Happy to provide any additional context and REALLY appreciate anyone's assistance here on what I hope is a straightforward issue.

Buttons
Button_PrintSummary
Button_PrintSummarywDetail

Code
Private Sub Button_PrintSummary_Click()
DoCmd.OpenReport "Rpt - SO by Customer", acViewPreview, "",, acViewPreview
Repots![Rpt - SO by Customer].Section(0).Visible = False
End Sub

> I chose AcDialog to set Modal and Popup to Yes automatically.

Try this. And try to write the name of the report in this way. I think that most of the specialized brothers support my words in naming reports, queries, tables and even the names of fields inside the table design. There is an article by (Gasman) that explains this.

DoCmd.OpenReport "Rpt_SO_by_Customer", acViewPreview, "", , strCriteria
Reports![Rpt_SO_by_Customer].Section(0).Visible = False

 

[Gasman]

Concatenation is all wrong.
Why not just pass in True or False and have
.Visible = OpenArgs
Use the whole name, but I am on my phone, so only typing what I need to.

 

[DJL910]

Concatenation is all wrong.
Why not just pass in True or False and have
.Visible = OpenArgs
Use the whole name, but I am on my phone, so only typing what I need to.

I didn't want to pass True or False because ultimately I will have multiple buttons (producing a variety of different 'versions') for the report output. I figured passing Level0, Level1, Level2 was a more scalable option for me.

I'm not even able to get to sorting out my concatenation on the OpenArgs side because the code doesn't move past the DoCmd OpenReport line.

 

[azhar2006]

Concatenation is all wrong.
Why not just pass in True or False and have
.Visible = OpenArgs
Use the whole name, but I am on my phone, so only typing what I need to.

DoCmd.OpenReport " Rpt_SO_by_Customer", acViewPreview, OpenArgs = True

 

[Gasman]

Have you tried compiling the code?

 

[DJL910]

So, it's the code I added in the Report that is preventing it from moving forward... I'll work to clean that up

 

[Gasman]

All you need (now at least) is

If OpenArgs = "Level0" Then
    Reports![Rpt - SO by Product].Section(0).Visible = False
Else
    Reports![Rpt - SO by Product].Section(0).Visible = True
End If

When you start having more, perhaps use a Select Case

Plus, as that code is in the report, you can shorten all that to
Me..Section(0).Visible

 

[DJL910]

So, I have this on my Form:

Private Sub Button_PrintSummaryProductLevel0_Click()

    DoCmd.OpenReport "Rpt - SO by Product", acViewPreview, , , , "Level0"
    
End Sub

And this on my Report:

Private Sub Report_Open(Cancel As Integer)

If OpenArgs = "Level0" Then
    Me.Section(0).Visible = True
Else
    Me.Section(0).Visible = False
End If
      
End Sub

AND ... I get the same Run-time Error '2501' TheOpenReport action was canceled on my DoCmd.OpenReport line. If I just open the Report directly, I get a message that "The expression On Open you entered as the event property setting produced the following error: A problem occurred while CApplication was communicating with the OLE server or ActiveX control. The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. There may have been an error evaluating the function, event, or macro.

I'm going to check all my names and references again.

The same occurred when I used the code:

    If OpenArgs = "Level0" Then
        Reports![Rpt - SO by Product].Section(0).Visible = False
    Else
        Reports![Rpt - SO by Product].Section(0).Visible = True
    End If

 

[DHookom]

Does your code compile?
What do you see with this code?
Private Sub Report_Open(Cancel As Integer)
MsgBox Me.OpenArgs
End Sub

 

[Gasman]

Comment that code out for now.
Can you then open the report?
Did you use intellisense for the OpenReport?

 

[DJL910]

Ayyy. So it threw the same error. I decided to create a new form, and add a new button. When I clicked to add the new button, I received:

"The code contains a syntax error, or a CApplication function you need is not available. If the syntax is correct, check the Controls Wizards subkey or the Libraries key in the CApplication section of the Windows Registry to verity that the entries are listed and available..."

I clicked OK and still entered the code. It didn't work and threw the 2501 error.
I checked other buttons in my application that DoCmd.Print Report and they worked.
I copied code from one of those into my new button and it worked.
I put my original code back in, and get the same error.
DoCmd.OpenReport "Rpt - SO by Product", acViewPreview, , , , "Level0"
I removed the OPENARGS code from the Report, and stripped the "Level0" argument and I'm back to square one.

I then added the following to my new button on my new form:
Private Sub Command3_Click()
DoCmd.OpenReport "Rpt - SO by Product", acViewPreview, , , , "Level0"
End Sub

And the suggested code into the Report
Private Sub Report_Open(Cancel As Integer)
MsgBox Me.OpenArgs
End Sub

And it threw the same 2501.


Unfortunately, at this point in my day, I have to move on to working on other parts of the application. I'll be able to revisit hopefully in the morning tomorrow, but I would appreciate any other troubleshooting suggestions. It seems to me that something is screwed up in the Code on the Report.

 

[DJL910]

Comment that code out for now.
Can you then open the report?
Did you use intellisense for the OpenReport?

When I comment out the code lines (If statement, if to end if), I get the same error.
When I also comment out the "Private Sub..." and "End Sub" line I can open the report without an issue.

 

[azhar2006]

As brother Gasman said, use a Select Case

Dim rpt As String
Dim c As Integer
c = Me.Frame1.Value
Select Case c

Case 1
rpt= "Level1"
Case 2
rpt = "Level2"
Case 3
rpt= "Level3"
Case 4
rpt= "Level4"

rpt = "Rpt_SO_by_Customer"

End Select
    DoCmd.OpenReport rpt, acNormal

 

[DJL910]

When I comment out the code lines (If statement, if to end if), I get the same error.
When I also comment out the "Private Sub..." and "End Sub" line I can open the report without an issue.

Ayyy, something somewhere must have been corrupted!!! I simply copied the report, added the "OnOpen" code as you all suggested, updated my buttons to direct DoCmd to the new form and all works as it should.

I appreciate all of the support, and I should be good to build-out as I need with a few if statements and the select case.

 

[DJL910]

SOLUTION:

Create a form with two buttons and assign the "On Click" code as follows. This drives the OpenArgs to the report (which is only available on an 'on open' event.

Private Sub PrintButton_RptSObyProductLevel0_Click()

    DoCmd.OpenReport "Rpt - SO by Product2", acViewPreview, , , , "Level0"
    
End Sub

Private Sub PrintButton_RptSObyProductLevel1_Click()

    DoCmd.OpenReport "Rpt - SO by Product2", acViewPreview, , , , "Level1"
    
End Sub

Then, within the "On Open" of the given Report, enter the following:

Private Sub Report_Open(Cancel As Integer)

If OpenArgs = "Level0" Then
    Me.Section(0).Visible = False
Else
    Me.Section(0).Visible = True
End If

End Sub