Run code before close report (2 Viewers)

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
Dear specialists,

I want to give the user the posibility to print the repport or not when they close the repport with this code :

Code:
Private Sub Report_Close()
Dim Response As Integer
Response = MsgBox("Printen?", vbYesNo + vbQuestion, "Report Information")
If Response = vbYes Then
DoCmd.PrintOut acPrintAll, , , , 1
Else
Exit Sub
End If
End Sub

Because they don't have the quick menu to print the repport, I disabled it so they can not go to the design mode...

But my code gives an error.

error 2585 during execution. You cannot perform this action while a form or a report event is being processed.

I can hear my printer starting but he doesn't print.

What am I doing wrong?

Thanks

Greetz

Pascal :cool:
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
I know I can add a button in reportview, but I want to know if it's possible to run code when closing report in printpreview.

Thanks

Greetz

Pascal :cool:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
what is the quick menu?
did you disable it?

abandon your code and instead create a "shortcut" (right-click) menu for your report.

1. on VBA, add reference to Microsoft Office XX.X Object Library
2. Inser New Module, and paste the following code:
Code:
Private Sub subCreateReportShortcut()
    'arnelgp
    Const SHORTCUT_NAME As String = "menu_print"
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    On Error Resume Next
    CommandBars(SHORTCUT_NAME).Delete
    Set cbar = CommandBars.Add(SHORTCUT_NAME, msoBarPopup, , False)
    Set bt = cbar.Controls.Add
    bt.Caption = "Print Report"
    bt.OnAction = "=fncPrint()"
    bt.FaceId = 4
    Set bt = cbar.Controls.Add
    bt.BeginGroup = True
    bt.Caption = "Close"
    bt.OnAction = "=fncCloseReport()"
End Sub

Public Function fncPrint()
On Error Resume Next
DoCmd.RunCommand acCmdPrintSelection
End Function

Public Function fncCloseReport()
    DoCmd.Close
End Function
3. click anywhere inside subCreateReportShortcut, and press F5 to run the sub (you only need to run this once).
you now have "menu_print" on your db.
4. open the report in design view.
5.on it's Property Sheet->Other->Shortcut Menu bar, type menu_print
save your report and view.
test the right-click.

you may also use same shortcut menu for your other report, just follow step# 4-5.
 
Last edited:

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

In all my forms I disabled the quickmenu (right mouse button)so the user can not go to the design mode of the form. As ADMIN they are enabled :p

And also they don't need a Quick menu, everything runs with buttons...

I was planning to do the same for my reports, that's why I asked the question regarding the code. Now I wil test your solution. I keep you informed.

Thanks a lot.

Greetz

Pascal :cool:
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

I'm having a compile error :

a user-defined data type is not defined :rolleyes:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
I tested it on disabled shortcut menu, and it won't show the print dialog.
so, you need to change it to:
Code:
Public Function fncPrint()
On Error Resume Next
'DoCmd.RunCommand acCmdPrintSelection
CommandBars.ExecuteMso "PrintDialogAccess"
End Function
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
Yep,

I forgot the Microsoft Office XX.X Object Library :banghead:

It's working…. :)

Thanks a lot

See you next time.

Greetz

Pascal :cool:
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
In my reports the schortcutmenu are not disabled yet, and now I don't have to I think.

If I don't disable them I don't have to change the code right?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
there's no need to change, just follow step#5.
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
Good morning arnelgp,

I'm experimenting a little and I added a 3th command in the short cut menu.

Code:
Private Sub subCreateReportShortcut()
    'arnelgp
    Const SHORTCUT_NAME As String = "menu_print"
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    On Error Resume Next
    CommandBars(SHORTCUT_NAME).Delete
    Set cbar = CommandBars.Add(SHORTCUT_NAME, msoBarPopup, , False)
    Set bt = cbar.Controls.Add
    bt.Caption = "Printen - Emprimer"
    bt.OnAction = "=fncPrint()"
    bt.FaceId = 4
    Set bt = cbar.Controls.Add
    bt.BeginGroup = True
    bt.Caption = "Close"
    bt.OnAction = "=fncCloseReport()"
    [COLOR="Red"]Set bt = cbar.Controls.Add
    bt.BeginGroup = True
    bt.Caption = "Design"
    bt.OnAction = "=fncDesign()"[/COLOR]
End Sub

Public Function fncPrint()
On Error Resume Next
    DoCmd.RunCommand acCmdPrintSelection
End Function

Public Function fncCloseReport()
    DoCmd.Close
End Function
[COLOR="Red"]Public Function fncDesign()
    DoCmd.RunCommand acCmdLayoutView
End Function[/COLOR]

but I'm having an error : :(

error 2046 during execution
the command or action layout view is currently unavailable

Even my "Allow Layout view" property of the report is set to Yes.

Or is it not that easy? :rolleyes:

Thanks

Greetz

Pascal :cool:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
Code:
Private Sub subCreateReportShortcut()
    'arnelgp
    Const SHORTCUT_NAME As String = "menu_print"
   'Dim cbar As CommandBar
   'Dim bt As CommandBarButton
    On Error Resume Next
    CommandBars("menu_print").Delete
    With CommandBars.Add(SHORTCUT_NAME, msoBarPopup, , False)
        'set bt=.Controls.Add
        With .Controls.Add
            .Caption = "Printen - Emprimer"
            .OnAction = "=fncPrint()"
            .FaceId = 4
        End With
        With .Controls.Add
            .BeginGroup = True
            .Caption = "Design"
            .OnAction = "=fncDesign()"
            .FaceId = 212
        End With
        With .Controls.Add
            .BeginGroup = True
            .Caption = "Preview"
            .OnAction = "=fncDesign()"
            .FaceId = 28
            .Visible = False
        End With
        With .Controls.Add
            .BeginGroup = True
            .Caption = "Close"
            .OnAction = "=fncCloseReport()"
        End With
    End With
End Sub

Public Function fncPrint()
On Error Resume Next
'DoCmd.RunCommand acCmdPrintSelection
CommandBars.ExecuteMso "PrintDialogAccess"
End Function

Public Function fncCloseReport()
    DoCmd.Close
End Function

Public Function fncDesign()
    Dim sReport As String
    sReport = Screen.ActiveReport.name
    If CurrentProject.AllReports(sReport).CurrentView = acCurViewLayout Then
        With CommandBars("menu_print")
            .Controls("Design").Visible = True
            .Controls("Preview").Visible = False
        End With
        DoCmd.Close acReport, sReport, acSaveNo
        DoCmd.OpenReport ReportName:=sReport, View:=acViewPreview
    Else
        With CommandBars("menu_print")
            .Controls("Design").Visible = False
            .Controls("Preview").Visible = True
        End With
        DoCmd.Close acReport, sReport, acSaveNo
        DoCmd.OpenReport ReportName:=sReport, View:=acViewLayout
    End If
End Function
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

That's indead a little diffirent :rolleyes:

Thank you so much. :):)

In my forms I enable or disable the shortcutmenu depending on user or admin as follow :

Code:
Dim Dienstlevel As Integer
Dienstlevel = DLookup("DienstID", "qryUsersDiensten", "UserLogin = '" & Forms!FormLogin.txtUserName.Value & "'")
 
Select Case [Forms]![FormLogin].[txtDienstID]
       Case 1, 2, 5, 8
            Me.ShortcutMenu = True
       Case Else
            Me.ShortcutMenu = False
       End Select

I renamed your first example so there is no confusion between 1st and 2nd module you give me.

Is it possible to switch between the 2 modules like I do in the forms? I know in the forms is it ShortcutMenu Yes or No...

In my case : if it's a user then module 5 (with print & close) and if it's admin module 7 (with print, designview, printpreview & close).

Or something like :
Code:
Case 1, 2, 5, 8
Me.ShortcutMenu = "menu_print"
Case Else
Me.ShortcutMenu = "Menu_print2"

Thanks

Greetz

Pascal :cool:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
yes, the custom shortcut menu is now part of your db.
but the one we made is for report?
you can make another menu for forms using same
techniques.
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

For the forms the users don't need a shortcutmenu. So it is not necessary to create a menu specially for forms. I disabled the standard shortcutmenu for users and enabled it for admins.

My question is can I activate the print_menu from #3 for users and the print_menu from #12 for admins in a way like I do for my forms?

With a code like this :

Code:
Dim Dienstlevel As Integer
Dienstlevel = DLookup("DienstID", "qryUsersDiensten", "UserLogin = '" & Forms!FormLogin.txtUserName.Value & "'")
 
Select Case [Forms]![FormLogin].[txtDienstID]
       Case 1, 2, 5, 8
            Me.ShortcutMenu = True
       Case Else
            Me.ShortcutMenu = False
       End Select

Now I have to select the print_menu on it's Property Sheet->Other->Shortcut Menu bar, type menu_print

My idea is something like this, but it doesn't work :banghead:

Code:
Dim Dienstlevel As Integer
Dienstlevel = DLookup("DienstID", "qryUsersDiensten", "UserLogin = '" & Forms!FormLogin.txtUserName.Value & "'")
 
Select Case [Forms]![FormLogin].[txtDienstID]
       Case 1, 2, 5, 8
            Me.ShortcutMenuBar = True
       Case Else
            Me.ShortcutMenuBar = ("menu_print")
       End Select

End Sub

Thanks

Greetz

Pascal :cool:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
Code:
Select Case [Forms]![FormLogin].[txtDienstID]
       Case 1, 2, 5, 8
            CurrentDb.Properties("AllowShortcutMenus") = True
       Case Else
            CurrentDb.Properties("AllowShortcutMenus") = False
       End Select
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
you need to save [Forms]![FomLogin]![txtDienstID] to a public variable, so you can access them in the report.
after logging in:
Code:
Tempvars.Add "allow_shortcut", (Instr(1, ",1,2,5,8", "," & [Forms]![FomLogin]![txtDienstID] & ",") > 0)
on each report, check this variable on its, Open event:
Code:
Private Sub Report_Open(Cancel As Integer)
If [Tempvars]![allow_shortcut] = False Then
    Me.ShortcutMenuBar = "menu_print"
End If
End Sub
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

I realy appreciate what you do, fantastic!!!


With you last code you disable all the shortcutmenu's for all users in forms and reports. But now the users can not print any report because I disabled the ribbon… :rolleyes:

That's why your code for small shortcutmenu's is so perfect.

In the beginning you created a shortcutmenu with : Print & Close (I called it print_menu2)

Later you created a shortcutmenu with Print, Close and DesignView (it's called print_menu)

Is it possible to give/activate for the users "print_menu2" and the admins (1,2,5,8) "print_menu"

In this way the users can print or close the report and the admin can also go to the design of the report.


Thanks a lot

Greetz

Pascal :cool:
 

scallebe

Registered User.
Local time
Today, 10:59
Joined
Mar 23, 2018
Messages
51
arnelgp,

So the first line of code (Tempvars.Add……etc.) I put in in my LoginForm?

Because I don't know what you mean with public variable… (sorry):confused:


Thanks again

Greetz

Pascal :cool:
 

Users who are viewing this thread

Top Bottom