Results 1 to 8 of 8
  1. #1
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23

    VBA Code to Hide Reports from Users

    I am newbie when it comes to this VBA stuff and need some help.
    I've got this code that hides the Queries and Tables which works great (the user has to log in to see these which only the admin can do)....however, I want to be able to hide the reports too but cant figure out the code to do this.
    Basically I want to hide the Reports from users unless they use the Switchboard.


    I know I can go into the Options, Navigation Panel and Hide Objects that way however a lot of my users know how to get into here so they can then see everything thats hidden, so I really need the code to hide it for me.
    Any help would be greatly appreciated. Thanks

    Click image for larger version. 

Name:	SetObjectHidden.JPG 
Views:	17 
Size:	50.3 KB 
ID:	26541

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can use 1 statement to hide the entire database. No need to cycle thru every object.

  3. #3
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    Quote Originally Posted by ranman256 View Post
    You can use 1 statement to hide the entire database. No need to cycle thru every object.
    That's great but how do you do that?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have a Switchboard, presumably with a reports option... so why not hide the database window?
    Click on FILE/OPTIONS/ Current Database.
    Look for the NAVIGATION section and uncheck "Display Navigation Pane". Click on OK to save changes.
    No code needed....

    Or did I misunderstand?

  5. #5
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    Hi ssanfu, I know the Navigation Pane can be hidden this way, however, my users know that if they hold down SHIFT while opening the database that the Navigation Pane will display, which really defeats the purpose of hiding it in the first place. However with the code above the tables and queries are hidden UNLESS they enter the admin login...the SHIFT key trick doesnt work with the code, which is why I am looking for a way to hide the reports using code. Thanks

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) Another thing you can try is to hide the report(s).

    Right click on the report and click on "Hide in this Group". The report will become grayed out. Do this for all reports you want to hide.
    Collapse all of the groups (right click on a header - TABLES for example), right click in the white area and click on "Navigation Options".
    At the bottom left of the dialog window (Display Options), ensure that "Show Hidden Objects" is unchecked.

    2) In addition, you can right click on the "Reports" header", then click on "Hide". This will hide the Reports group.


    3) Convert the accdb to accde??


    4) Have you seen this? https://support.microsoft.com/en-us/kb/826765

  7. #7
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    So I did as you suggested and hide the Reports Group. Then because a lot of my users know the trick of holding shift to bypass everything so they can see everything that is hidden I also found the below code to disable the Shift key trick Thanks a lot for your help

    I've put the code below in case someone else needs it.


    Function ap_DisableShift()
    'This function disable the shift at startup. This action causes
    'the Autoexec macro and Startup properties to always be executed.

    On Error GoTo errDisableShift

    Dim db As DAO.Database
    Dim prop as DAO.Property
    Const conPropNotFound = 3270

    Set db = CurrentDb()

    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False

    'The function is successful.
    Exit Function

    errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, False)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If

    End Function

    Function ap_EnableShift()
    'This function enables the SHIFT key at startup. This action causes
    'the Autoexec macro and the Startup properties to be bypassed
    'if the user holds down the SHIFT key when the user opens the database.

    On Error GoTo errEnableShift

    Dim db as DAO.Database
    Dim prop as DAO.Property
    Const conPropNotFound = 3270

    Set db = CurrentDb()

    'This next line of code disables the SHIFT key on startup.
    db.Properties("AllowByPassKey") = True

    'function successful
    Exit Function

    errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, True)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If

    End Function


    NOTE: In the Visual Basic Editor, click View menu and Immediate Window. Type ap_DisableShift and then ENTER in the Immediate Window to disable the SHIFT Key.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Then because a lot of my users know the trick of holding shift to bypass everything so they can see everything that is hidden
    How does that work. Holding down the shift key does not show the hidden objects if they are hidden.

    So they would be able to see these tables??? (how many tables/)
    Just curious....
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hide objects and menu bar from the users
    By Blancorn in forum Access
    Replies: 1
    Last Post: 04-10-2016, 06:08 PM
  2. Novice Users and Reports Best Practices
    By james28 in forum Reports
    Replies: 4
    Last Post: 03-02-2014, 03:35 PM
  3. Create reports for selected users
    By pfales in forum Reports
    Replies: 5
    Last Post: 04-30-2013, 08:50 AM
  4. How To Hide Duplicated Entire Row In Reports
    By vdanelia in forum Reports
    Replies: 1
    Last Post: 09-29-2011, 08:10 AM
  5. Hide tables from general users
    By stryder09 in forum Access
    Replies: 0
    Last Post: 03-28-2011, 11:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums