Display only last characters in a textbox

Question

I have an Access 2002 database/application where my clients can enter multiple information about their own clients, including a code which follow some rules.

However, when they view these information after they have been entered, I need to hide every characters in this code except for the 4 last characters. However, the agent needs to be able to edit this code if it needs to be modified.

So basically, I have 3 phases possible:

1. First time information are filled, empty data. The field must show the characters entered.
2. At a later date, the code must be hidden in some way to show only the last 4 characters. It can be with * or simply the last 4 characters, but the user must not be able to see what is before these.
3. The agent edit the code, the code must then be properly modified in the database. The characters must be shown.

I tried to show only the 4 last characters, however my database gets modified... So the code gets cut in the database.

Answer 1

I wrote the following function to obscure sensitive data. Its primary use is to discourage shoulder surfing. I'm not sure if it will meet your particular needs, but it is simple, straightforward and may help others who stumble upon this question.

'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 Open   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

Answer 2

Wow - I'm shocked that this hasn't been answered sufficiently. The best answer is to use an unbound text box in your form instead of a bound one. First you'll need to make your unbound text box populate the actual field. You'll do that in the AfterUpdate event.

Private Sub UnboundTextBox_AfterUpdate()
    [MyField] = Me.UnboundTextBox 
End Sub

Then you'll need to set an OnCurrent event to populate your unbound text box with the protected view whenever the agents view the record:

Private Sub Form_Current()
    Me.UnboundTextBox = String(Len([MyField])-4, "*") & Right([MyField], 4)
End Sub

However, you also want to let your agents edit or view the full code later, if necessary. The best way to do this would be to set the OnEnter event for your unbound text box to pull the whole field value, so the agent can see and edit it - effectively the reverse of your OnUpdate event.

Private Sub UnboundTextBox_Enter()
    Me.UnboundTextBox = Nz([Field1]) 'The Nz deals with Null errors
End Sub

I've used this with a field displaying SSN's and it works like a charm.