Mask all but last four digits of field

[buratti]

This has probably been asked a thousand times, but even with all my searches I still cannot find the correct answer for my situation. I need to mask a SSN field and display only the last 4 digits so it would display like "XXX-XX-1234"

I figured out that I can just set the control source to ="XXX-XX-" & right([SSN], 4) or something similar, but I am using the same form for data entry (new records) as well as viewing existing ones. If I am entering a new record I cannot enter the SSN in that field because it would be an unbound calcualted field.

So to sum up, what I need is when I am entering a new record in my form I need to SSN field to be able to accept input WITHOUT masking (to verify correct entry), but after the record is saved and reopened, that SSN field is now displaying "XXX-XX-1234"

I have thoughts on how to start it, but maybe someone can fill in the blanks for me...

My thought are... (rough draft)

OnCurrent
if me.newrecord then
   whatever code to show all digits but also keeping an input mask of the "999-99-9999" format
Else
    whatever code that will "x" out the first 5 digits
End If

Any thoughts???

 

[pr2-eugin]

Well it might not be the best way, but it is one way to do it.. Leave the unbound column as it is, I mean with the calculation with not being bound to the field in the table (however I prefer it to be calculated in VBA), BUT have it sit exactly on top of the Bound control, and also make it invisible (i.e. set the Visible property to No in design view), then in your Form current method..

Private Sub Form_Current()
    If Not Me.NewRecord Then
        Me.[B][COLOR=Blue]unboundCalculatedControlName[/COLOR][/B].Visible = True
        ' Me.[B][COLOR=Blue]unboundCalculatedControlName [/COLOR][/B][COLOR=Blue][COLOR=Black]= [/COLOR][/COLOR]"***-**-" & Right([SSN],4)
    End If
End Sub

 

[John Big Booty]

Another method would be to have an unbound text box sitting directly on top of your bound text box, set your Bound Text box's Input mask as desired and then in your form's On Current event have some code along the lines of;

    If Me.NewRecord = True Then
        Me.YourBoundTextBox.Visible = True
        Me.YourUnboundTxtBox.Visible = False
    Else
        Me.YourBoundTextBox.Visible = False
        Me.YourUnboundTxtBox.Visible = True
        Me.YourUnboundTxtBox = "***-**-" & Right(Me.YourBoundTextBox, 4)
    End If

 

[pr2-eugin]

Another method would be to have an unbound text box sitting directly on top of your bound text box, set your Bound Text box's Input mask as desired and then in your form's On Current event have some code along the lines of;

    If Me.NewRecord = True Then
        Me.YourBoundTextBox.Visible = True
        Me.YourUnboundTxtBox.Visible = False
    Else
        Me.YourBoundTextBox.Visible = False
        Me.YourUnboundTxtBox.Visible = True
        Me.YourUnboundTxtBox = "***-**-" & Right(Me.YourBoundTextBox, 4)
    End If

So its just not me who thought it that way..

 

[John Big Booty]

So its just not me who thought it that way..

Yes, I was having a play with various scenarios, and you posted first, but I don't think I read your reply fully

 

[John Big Booty]

Just notice; I think you might have a problem with your code if the users goes to an old record and then back to a new one, as you've not re-hidden the unbound text box in that instance.

You have also commented out the line of the code to populate your unbound text box

 

[buratti]

Thanks good thoughts...I tried that and it is working.

 

[pr2-eugin]

Just notice; I think you might have a problem with your code if the users goes to an old record and then back to a new one, as you've not re-hidden the unbound text box in that instance.

Yea, should have an else part there.. My bad..

You have also commented out the line of the code to populate your unbound text box

I did that because I mentioned that the OP could leave the Control source of the unbound to be as it was before.. but also said that it can be done via VBA, hence commented out..

 

[John Big Booty]

...

I did that because I mentioned that the OP could leave the Control source of the unbound to be as it was before.. but also said that it can be done via VBA, hence commented out..

That would be my bad then I guess I shouldn't do this when I should really be stacking Z's :banghead:

I'd best try and get back to that task

 

[nanscombe]

A different take on the task.

You would just need to make sure that the name of the field was not the same as the ControlSource.

Private Sub Form_Current()
Dim strSource As String
If Me.NewRecord Then
    strSource = "yourControlSource"
Else
    strSource = "=""XXX-XX-"" & Right([yourControlSource],4)"
End If

If Me.yourControlName.ControlSource <> strSource Then Me.yourControlName.ControlSource = strSource
End Sub