Hiding Columns in a Split Form MS Access

Question

I have two buttons on my main form. One of them is for the users to log feedback and the other is for me to go in and add notes/status updates for that feedback. Both buttons open the same form in Split Form view. I would like to hide specific columns when the users click their button vs myself clicking the admin button. What I have tried: Both buttons open the same form, however before the form is opened a public sub is called which a boolean variable to either true (for the users button) or false (for my button). Then when the on load procedure fires, it checks the variables value and either attempts to hide the columns or does not. Here is that code:

Option Compare Database

Public booFeedback              As Boolean

Public Sub FeedBackBoolean1()
booFeedback = False
End Sub


Public Sub FeedBackBoolean2()
booFeedback = True
End Sub

Then when the form itself loads it runs this code:

Private Sub Form_Load()

Call cmdRemoveFilter_Click
If booFeedback = False Then
    Me.colPriority.ColumnHidden = True
    Me.colWorkEffort.ColumnHidden = True
    Me.colStatus.ColumnHidden = True
    Me.colDeliveryDate.ColumnHidden = True
    Me.colStatusComments.ColumnHidden = True
Else
    Me.colPriority.ColumnHidden = False
    Me.colWorkEffort.ColumnHidden = False
    Me.colStatus.ColumnHidden = False
    Me.colDeliveryDate.ColumnHidden = False
    Me.colStatusComments.ColumnHidden = False

End If
End Sub

The code runs correctly however the results do not show (the columns that I am looking to hide are not hidden correctly). I have found that if I go into design mode, then close and save, the code works correctly the next time that I run it. However if I click the other button then the results are the same as the last time I ran it. Also, I have put pseudo password on the admin button so that noone can click into it. Not too worried about security as of now, will be down the road.

June7 June7 · Accepted Answer · 2017-03-07T19:39:24

For a starter, can simplify code, don't need the If Then Else:

Me.colPriority.ColumnHidden = Not booFeedback
Me.colWorkEffort.ColumnHidden = Not booFeedback
Me.colStatus.ColumnHidden = Not booFeedback
Me.colDeliveryDate.ColumnHidden = Not booFeedback
Me.colStatusComments.ColumnHidden = Not booFeedback

Now debug, set breakpoint, what is value of booFeedback during execution? If not as expected, review code logic. Right off, if booFeedback and those two subs are in a general module, I don't see a reason for failure.

I would have one button that simply opens form and code behind that form (possibly eliminate the public variable and subs) that would determine if the user is admin authorized and show/hide fields as appropriate. Can have a users table that stores Windows user IDs and permission levels. Then code can retrieve user Windows ID from the system and match to record in table. Or can hard code the admin user ID. Retrieve Windows user ID with: Environ("USERNAME") - yes, type it exactly like that.

booFeedback = Environ("USERNAME") = "adminWindowsUsername"

Is this a split database design? Is the navigation pane hidden? Are function keys disabled so users can't expand the pane? Is right click shortcut menu disabled? Are you (and maybe users) aware of shift key bypass that overrides these project settings when opening db?

Hiding Columns in a Split Form MS Access

1 Answers