Results 1 to 7 of 7
  1. #1
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30

    Hide a column in a report based on user rights

    The title says it all. I have user rights set up(in a module called globals) and they control access and viewing capabilities throughout the database based on the user login. I have not run into any issues with it except for with my reports. So, here it is. I have a report that is generated from a query. The query is then used as a record source for a sub form. I have one column(txtcommissiondue) that needs to be hidden from certain users, and displayed for others. On the subform itself, my code works fine. When a user doesn't have access, they don't see it. When users have access, they do see it. When they go to print the report from the subform, the column will show up regardless of user rights. My solution was to apply the code to the report as well, but that is not working

    Private Sub Form_Load()
    If Globals.UserAccess(Me.Name) = False Then


    Forms!frmPurchaseOrders!PurchaseOrderssub.Form.txt CommissionDue.ColumnHidden = -1
    End If
    End Sub

    However, when I try this on a report it hides it no matter what user is logged in, and throws that Enter Parameter Value pop up at me.

    Private Sub Report_Load()
    If Globals.UserAccess(Me.Name) = False Then
    Reports!rptPurchaseOrders.txtCommissionDue.Visible = False
    Reports!rptPurchaseOrders.CommissionDue_lbl.Visibl e = False
    End If
    End Sub

    If anyone can point me in the right direction, i would appreciate it.

    Thanks

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try your code in the On Format event of the Detail Section
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    No dice. Now the txtCommissionDue shows, and the enter parameter value box still pops.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Me.Name is for passing the name of the form or report to the global procedure? Surely it is not the name of a control on the form or report?
    Maybe step through your code and see what the variables are holding. I'm assuming the report control is not on a sub report.
    What value is the prompt asking for?

    CommissionDue_lbl.Visibl e
    you are getting injected spaces because you're not using code tags. That might throw off some of the answers you get.
    Last edited by Micron; 06-29-2017 at 01:33 PM. Reason: added question
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    Me.Name is for passing to the global procedure, it is not the name of a control. The report control is not on a sub report. The prompt is asking for the parameter value of txtCommissionDue.
    CommisionDue_lbl.Visibl e = saw that. The code is correct(without injected spaces) in the database. Just failed to use code tags on here.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So it doesn't recognize txtCommissionDue. I see that you have CommissionDue_lbl, as if the control name doesn't start with txt. Would explain the problem with the visible property.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    Thanks Micron.

    I have both on there because the actual column would hide, but the label(column header) would stay. So i turned both off. I ended up fixing the issue. I created a new report(with the same exact query) and used the exact code I had listed, and voila...it worked. I have no idea what was causing the bug before, but it is now working properly.

    Thanks again.

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

Similar Threads

  1. Log on form and user rights
    By HS_1 in forum Forms
    Replies: 18
    Last Post: 12-28-2016, 07:56 PM
  2. Replies: 1
    Last Post: 10-27-2016, 10:51 PM
  3. Replies: 1
    Last Post: 03-12-2014, 03:13 AM
  4. Hide field in sub-report based on condition
    By vinsavant in forum Reports
    Replies: 4
    Last Post: 12-16-2012, 10:18 AM
  5. User always appear to connect with exclusive rights
    By kblinkhorn in forum Security
    Replies: 18
    Last Post: 09-02-2010, 01:07 PM

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