Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2019, 09:46 AM   #1
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Run code before close report

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

scallebe is offline   Reply With Quote
Old 09-13-2019, 10:16 AM   #2
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

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
scallebe is offline   Reply With Quote
Old 09-13-2019, 10:29 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Run code before close report

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.

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 09-13-2019 at 10:46 AM.
arnelgp is offline   Reply With Quote
Old 09-13-2019, 10:50 AM   #4
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

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

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
scallebe is offline   Reply With Quote
Old 09-13-2019, 11:04 AM   #5
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

arnelgp,

I'm having a compile error :

a user-defined data type is not defined
scallebe is offline   Reply With Quote
Old 09-13-2019, 11:05 AM   #6
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

wait… I know what I forgot...
scallebe is offline   Reply With Quote
Old 09-13-2019, 11:07 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Run code before close report

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-13-2019, 11:08 AM   #8
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

Yep,

I forgot the Microsoft Office XX.X Object Library

It's working….

Thanks a lot

See you next time.

Greetz

Pascal
scallebe is offline   Reply With Quote
Old 09-13-2019, 11:11 AM   #9
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

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?
scallebe is offline   Reply With Quote
Old 09-13-2019, 11:16 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Run code before close report

there's no need to change, just follow step#5.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
scallebe (09-13-2019)
Old 09-13-2019, 09:42 PM   #11
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

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()"
    Set bt = cbar.Controls.Add
    bt.BeginGroup = True
    bt.Caption = "Design"
    bt.OnAction = "=fncDesign()"
End Sub

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

Public Function fncCloseReport()
    DoCmd.Close
End Function
Public Function fncDesign()
    DoCmd.RunCommand acCmdLayoutView
End Function
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?

Thanks

Greetz

Pascal
scallebe is offline   Reply With Quote
Old 09-14-2019, 12:25 AM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Run code before close report

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-14-2019, 01:35 AM   #13
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

arnelgp,

That's indead a little diffirent

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
scallebe is offline   Reply With Quote
Old 09-14-2019, 01:46 AM   #14
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Run code before close report

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-14-2019, 05:28 AM   #15
scallebe
Newly Registered User
 
Join Date: Mar 2018
Posts: 51
Thanks: 29
Thanked 1 Time in 1 Post
scallebe is on a distinguished road
Re: Run code before close report

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

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

scallebe is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Close Code ECEK Forms 6 11-18-2016 04:24 AM
Open recordset on report open, close on report close bulrush Reports 2 03-14-2011 09:48 AM
Close report code not working jd_boss_hogg Modules & VBA 2 09-20-2009 11:22 PM
Code to close a db? Dudley Modules & VBA 4 09-02-2008 02:22 PM
close the code? miki Forms 1 10-28-2001 09:17 PM




All times are GMT -8. The time now is 03:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World