VBA For Last 4 SSN Format Text Box

[data808]

I have a text box that the user needs to type only the last 4 digits of the customer's SSN. I want the format to be:



XXX-XX-1234

Not sure how I should go about it yet but one idea I had was to make the default value of that text box "XXX-XX-" then the user can just fill out the remainder. Then put VBA to check that the value always has those X's and dashes with 4 numbers following it before saving the record. I don't know to write the VBA to do the check for this format.

Can someone help with that? Thanks.

[June7]

Why save data with all those Xs? Just save the 4 digits and format to display with Xs when needed.

Have you tried InputMask property?

[data808]

True. Will try that and let you know June7. Thanks.

[data808]

I've decided to make the "XXX-XX-" a label right next to the text box that the user will type the last 4 digits of the SSN. What I need to do now is to make the label hidden if the user does not type into the last 4 digits text box. I was thinking of doing this in the On Current Form event something like this:

If isnull (me.txtLast4SSN).value then
me.lblLast4SSN = hidden
else
me.lblLast4SSN = unhidden
exit sub
end if

Just not sure how to write code to make labels hidden. Could you help with that?

[data808]

Came up with this but the problem I am having is finding the right even for it to work properly. I tried the On Current event for the form but it only triggers in the beginning when I open the form. Then I tried in the dirty but once you type and the form is dirty you can delete the text and make the label invisible again. Basically I want the form to start off with the label invisible until the clerk types into that txtLast4SSN text box. I also want the label to disappear if the clerk changes their mind and deletes the value in that text box before they save and what not.

If isnull (me.txtLast4SSN).value then
If isnull (me.txtLast4SSN) then
me.lblLast4SSN.visible = false
else
me.lblLast4SSN.visible = true
end if

[June7]

Sometime more than one event is needed. Use Current and textbox AfterUpdate.

Is this a Continuous form? Changing control property will affect all instances of control.

But why is this so important? Why bother?

[data808]

Sometime more than one event is needed. Use Current and textbox AfterUpdate.

Is this a Continuous form? Changing control property will affect all instances of control.

But why is this so important? Why bother?

It’s all about making the UI intuitive and easy to understand how functions work within the form. I like when things are automated and very user friendly which is why I try to cover scenarios like the clerk changing their mind and backspacing the value in the text box which logically should make the label go back to being invisible again since the X's are not necessary anymore. The X's are only relevant if the user types the last 4 digits of the SSN to make the form look nice for printing. I know printing forms is not practical and frowned upon and it should be done with a report but right now this is what I am using and it’s working great for the time being. In the future I may migrate this form to a report but for now it's fine.

I'll try a bit tomorrow with those events that you suggested and see what I can accomplish. Thanks for the help.

Oh btw, is there a way to change my status from "Expert" to "Beginner" on this forum? Don't know how I ended up getting that kind of title as I feel like I'm still a noob at Access. lol

[June7]

Never thought much about that. Click on Settings at top of page. Then click Edit Profile. Either Reset or enter whatever you want to show for Custom User Title.

[isladogs]

In this forum, user status is automatically assigned according to the number of posts. So you start as a beginner but it is assumed after 600+ posts that you have become an expert!
The system does have obvious flaws. Anyway, as June stated you can overwrite with your own custom status if you wish e.g Access noob

[Micron]

You might be making this more complicated than is necessary, but then you didn't answer why you need the x's. Why isn't the label permanent ("Last 4 SSN Characters"), make the control wide enough for X's and last four, then when record is saved, prepend 1234 with x's and save that. Then no matter which record you move to, it will show X's then 1234.
If not that, there must be a simple way. I think our problem is that we don't have the whole picture.

[data808]

You might be making this more complicated than is necessary, but then you didn't answer why you need the x's. Why isn't the label permanent ("Last 4 SSN Characters"), make the control wide enough for X's and last four, then when record is saved, prepend 1234 with x's and save that. Then no matter which record you move to, it will show X's then 1234.
If not that, there must be a simple way. I think our problem is that we don't have the whole picture.

Thanks for the help. So basically I simplified it to 2 events that seems to be working perfectly. Here it is:

On Current event for the form = Me.lblLast4SSNJUV.Visible = True

Then I have a save/print button. On the click event it will do a check for a value typed into the text box. If no value is typed in then it will make the label with X's disappear, save, then print the record. If a value is typed in the text box then it will continue to keep the label visible, save, then print record. Here is the code without the save and print commands:

If IsNull(Me.txtLast4SSNJUV) Then
Me.lblLast4SSNJUV.Visible = False
Else
Me.lblLast4SSNJUV.Visible = True
End If

Thank you very much for all the help and sorry if I left out too many details. I have a hard time figuring out what info I need to give in order to get the results I want. I usually give the details slowly as more questions arise from the responses.

[Micron]

If no value is typed in then it will make the label with X's disappear,

If there is no value, why not just hide the control - then the label can't be seen either. Or must the label not be attached to the control for some reason? What you have could possibly also be written as

If IsNull(Me.txtLast4SSNJUV) Then Me.lblLast4SSNJUV.Visible = Not IsNull(Me.txtLast4SSNJUV)
or

If IsNull(Me.txtLast4SSNJUV) Then 
   Me.lblLast4SSNJUV.Visible = Not IsNull(Me.txtLast4SSNJUV)
End If

[data808]

If there is no value, why not just hide the control - then the label can't be seen either. Or must the label not be attached to the control for some reason? What you have could possibly also be written as

If IsNull(Me.txtLast4SSNJUV) Then Me.lblLast4SSNJUV.Visible = Not IsNull(Me.txtLast4SSNJUV)
or

If IsNull(Me.txtLast4SSNJUV) Then 
   Me.lblLast4SSNJUV.Visible = Not IsNull(Me.txtLast4SSNJUV)
End If

I want the X's there to indicate to the user that they only need to type the last 4 digits of the SSN if they choose to use the text box.

[Micron]

That's what labels do? You could put a 4 character limit on the field and your message would be raised. Anyone who gets the message isn't paying attention in the first place. Anyone who gets it repeatedly, well you've probably read the definition of insanity.

[data808]

That's what labels do? You could put a 4 character limit on the field and your message would be raised. Anyone who gets the message isn't paying attention in the first place. Anyone who gets it repeatedly, well you've probably read the definition of insanity.

Haha yes some users aren't very good at paying attention to prompts and labels and I try to keep that in mind when designing forms. I want it as simple and intuitive as possible so I don't need to explain instructions to the users. Anyway thanks for the help. I'll use what I got for now but if the users aren't clever enough to figure out what data I want from them then I'll revisit some of these suggestions. lol Thanks again.