Version 1.42 Approx 0.8 MB (zipped) First Published 16 Nov 2022 Last Updated 18 Nov 2022
A few months ago, Mike Wolfe published an article on his NoLongerSet website: ObscureInfo(): Hide Sensitive Information in Access Form Controls
As a belated follow up to Mike's article, I thought I would share several approaches I have used for many years to hide sensitive data in both forms and reports.
For example, many of the reports I created for my commercial databases for schools contain confidential data only intended for users with sufficient security. Rather than create two types of each report, I tagged the fields as 'ANON' that unauthorised people shouldn't see and then hid those fields when printed / in print preview. This allowed me to have a version of each report that could be shown in meetings to e.g. all staff or governors for info or training purposes with no risk of showing confidential student/staff/whole school data
A similar process was used for forms with confidential data.
Where an entire form was confidential, access was restricted to authorised users and a password was required.
The example app supplied with this article also includes Mike Wolfe's approach so you can compare the methods used and choose whichever best suits your needs.
In some cases, you can also combine more than one approach
The main purpose is to prevent accidental disclosure where sensitive data is made visible to unauthorised users close to your workstation
I have added some additional security such as masked input boxes, encrypted passwords and VBA passwords.
All these additional security features are optional and can be omitted if not required for your situation
None of the methods are intended to be a solution to prevent deliberate hacking.
In a real world situation, make sure your application is distributed as an ACCDE file to provide any real protection.
Main Form
The main form has three different options for hiding sensitive data on forms:
1. Using the Tag property and an encrypted password
2. Using the Tag property with click and mouse events
3. Using Mike Wolfe's ObscureInfo function
Each method has advantages and disadvantages depending on your particular setup
In addition, it shows one approach to achieving similar results in reports - again using the Tag property
All 3 forms use the same data and look very similar.
Form 1
The data in 6 fields is being treated as sensitive. The controls for those fields are initially shown redacted so the sensitive data is hidden
To do this, all 6 controls are assigned the same Tag property value : HD (for Hide Data)
The Form_Load and Form_Current events both contain the code line HideSensitiveData
The HideSensitiveData procedure is in the module modFunctions along with its sister procedure ShowSensitiveData
CODE:
Public ctl As control
' ----------------------------------------------------------------
' Procedure : HideSensitiveData
' Date : 17/10/2008
' Author : Colin Riddington
' Web page : https://isladogs.co.uk/hide-sensitive-data/
' Purpose : Hide / redact data in sensitive fields (e.g., DOB, Address, Phone)
' Usage : Checks for controls with specified tag value HD
' Redacts data by setting the control backcolor to match the forecolor
' Also prevents tabbing and locks the control
' Use in Form_Load and Form_Current events where control value is NOT null
' ----------------------------------------------------------------
Public Sub HideSensitiveData()
On Error GoTo Err_Handler
For Each ctl In Screen.ActiveForm.Controls
If Nz(ctl.Value, "") <> "" Then
If ctl.Tag = "HD" Then 'hide Data
ctl.BackColor = ctl.ForeColor
ctl.Locked = True
ctl.TabStop = False
End If
Else
ctl.BackColor = Screen.ActiveForm.Detail.BackColor
End If
Next ctl
Exit_Handler:
Exit Sub
Err_Handler:
'errors occur if form not fully loaded - these can be bypassed
If Err = 2475 Or Err = 2467 Or Err = 91 Or Err = 92 Or Err = 438 Then Resume Next
MsgBox "Error " & Err & " in HideSensitiveData procedure ; " & Err.Description
Resume Exit_Handler
End Sub
' ----------------------------------------------------------------
' Procedure : ShowSensitiveData
' Date : 17/10/2008
' Author : Colin Riddington
' Web page : https://isladogs.co.uk/hide-sensitive-data/
' Purpose : Show redacted data in sensitive fields (e.g., DOB, Address, Phone)
' Usage : Checks for controls with specified tag value HD
' Also re-enables tabbing and unlocks the control
' Resets the control backcolor to match the form detail backcolor
' ----------------------------------------------------------------
Public Sub ShowSensitiveData()
On Error GoTo Err_Handler
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "HD" Then 'show data
ctl.BackColor = Screen.ActiveForm.Detail.BackColor
ctl.Locked = False
ctl.TabStop = True
End If
Next ctl
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err & " in ShowSensitiveData procedure ; " & Err.Description
Resume Exit_Handler
End Sub
When the fields are redacted, users cannot copy / paste the hidden data. To prevent this:
a) the controls are locked and tabbing disabled
b) the right click shortcut menu is disabled
b) the use of the Ctrl+C and Ctrl+V keys is disabled by setting KeyPreview = Yes in the form property sheet and using the following code:
CODE:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'disable Ctrl+C & Ctrl+V in controls with sensitive data by resetting keys to nothing
'must also set form Key Preview = Yes in property sheet
Dim intCtrlDown As Integer
If Me.ActiveControl.Tag = "HD" Then
intCtrlDown = (Shift And acCtrlMask) > 0
If KeyCode = vbKeyC Then
If intCtrlDown Then
'Ctl + C disabled
KeyCode = 0
End If
End If
If KeyCode = vbKeyV Then
If intCtrlDown Then
'Ctl + V disabled
KeyCode = 0
End If
End If
End If
End Sub
For more details on this approach, see my article Prevent Copy & Paste in Access Forms
To view the data, click the Show Sensitive Data button and enter the password 'isladogs' in the input box that appears.
I have used a masked input box here so that the entered data is obscured
If the password matches, the ShowSensitiveData procedure runs. All the hidden data is made visible and it can be edited
To hide the data, click the button again - it is now captioned Hide Sensitive Data
Alternatively, the fields are automatically redacted when you move to another record..
This occurs because the Form_Current event also includes the line HideSensitiveData
NOTE:
a) For the purpose of this DEMO, you can click the Help button (with a question mark) if you forget the password
b) 128-bit encryption is applied to the password using the RC4 function.
For more details about RC4 encryption, see my article in the security challenges section of this website:
Securing Your Data - An Encrypted Split No Strings Database
The CheckPWD function compares the encrypted password with that entered.
Form 2
One possible issue with the approach used in Form 1 is that all data is made visible when you may only want one field to be shown.
This issue is addressed in Form 2 which has 3 possible ways of viewing the data. Once again this uses the Tag property and the HideSensitiveData / ShowSensitiveData procedures
These are as follows:
a) All redacted fields are made visible when you hold down the left mouse button on the Show Sensitive Data button
Release the mouse and the fields are all redacted again (if they contain data)
This method allows you to view the data but not edit it
b) Click or tab to any redacted control to view/edit it. Click or tab away from the control to automatically hide the data again
This uses two very simple form procedures: ShowControlData and HideControlData
CODE:
Private Sub ShowControlData()
'show data in active control on enter event
Me.ActiveControl.BackColor = Me.Detail.BackColor
End Sub
Private Sub HideControlData()
'hide data in active control on exit event if it contains data
If Nz(Me.ActiveControl, "") <> "" Then Me.ActiveControl.BackColor = Me.ActiveControl.ForeColor
End Sub
The ShowControlData procedure runs on the Enter event and the HideControlData runs on the Exit event on each of the redacted controls.
In addition, the form Detail_MouseMove event is used to set the focus to another control
CODE:
Private Sub Address_Exit(Cancel As Integer)
HideControlData
End Sub
Private Sub Address_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
ShowControlData
End Sub
Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
'move focus away from previous control so it can be clicked again
cmdClose.SetFocus
End Sub
c) Double click the word Contacts in the form header section. Enter the password exactly as in Form 1.
All fields are now visible and can be edited
Form 3
This uses the code supplied by Mike Wolfe in his article ObscureInfo(): Hide Sensitive Information in Access Form Controls
This is a 'low code approach with just one function: ObscureInfo in module modObscure
CODE:
' ----------------------------------------------------------------
' Procedure : ObscureInfo
' Date : 21/09/2009
' Author : Mike Wolfe
' Source : https://nolongerset.com/obscureinfo/
' Purpose : Use to hide data in sensitive fields (e.g., BirthDate, PhoneNum, SSN)
' Usage : Ctl OnEnter property: =ObscureInfo(False, Form.ActiveControl)
' Ctl OnExit property: =ObscureInfo(True, Form.ActiveControl)
' Form Current property: =ObscureInfo(True, [BirthDate], [HomePhone], [SSN])
' Form Load property: =ObscureInfo(True, [BirthDate], [HomePhone], [SSN])
' ----------------------------------------------------------------
Function ObscureInfo(HideIt As Boolean, ParamArray Ctls() As Variant)
Dim ctl As Variant
For Each ctl In Ctls
If HideIt Then
If IsNull(ctl.Value) Then
ctl.BackColor = vbWhite
Else
ctl.BackColor = ctl.ForeColor
End If
Else
ctl.BackColor = vbWhite
End If
Next ctl
End Function
All other functionality is done from the property sheet both for the form and the controls to be redacted
The ObscureInfo function is set True for ALL the controls to be redacted in both the Form_Load and Form_Current events
The function is then set False in the Enter event of those controls and True again in the Exit event
The result is that the data in an individual control is made visible when it is entered and hidden again on exit
For more details of this approach, see Mike Wolfe's article
The main advantage of this method is that it is low code
However, setting the function values in the property sheet is perhaps a bit more work than using the Tag property
Reports
Reports can also be managed using the Tag property. By default, the sensitive data fields are hidden in the report
The fields are made visible by unticking the checkbox on the main form.
This warning message appears.
Click YES to show ALL the data
The form's Print button code is:
Private Sub cmdPrint_Click()
If Me.chkHideData Then
DoCmd.OpenReport "rptContacts", acViewPreview, , , , "Hide"
Else
DoCmd.OpenReport "rptContacts", acViewPreview
End If
DoCmd.RunCommand acCmdZoom100
End Sub
The code in the report is in the Detail_Format event.
This checks if the report has been run with OpenArgs = "Hide" and sets the controls visible or hidden as appropriate.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.OpenArgs = "Hide" Then
For Each ctl In Me.Controls
If ctl.Tag = "HD" Then 'hide Data
ctl.ForeColor = ctl.BackColor
End If
Next
Me.lblHide.Visible = True 'show info label
Else
Me.lblHide.Visible = False 'hide info label
End If
End Sub
That's all there is to it! Very simple to implement but also very effective for its purpose
Version History
v1.40 15/11/2022 - initial release
v1.42 18/11/2022 - added code to prevent users editing or copying redacted data in Form1
Download
Click to download: Hide Sensitive Data_v1.42 (zipped)
Feedback
Please use the contact form to let me know whether you found this article / example app useful or if you have any questions.
Colin Riddington Mendip Data Systems Last Updated 18 Nov 2022
|
Return to Example Databases Page
|
Return to Top
|