StrConv to display as SSN

How can I use STRCONV to display the data in my unbound combobox as SSN?

Some data is displaying as a String and I wanted to make sure the data is in the proper format


Thanks!
Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure I understand the STRCONV.  You just need a format specified:

###-##-####

Jim.
Ernest GroggSecurity Management InfoSecAuthor Commented:
In my ComboBox it shows it as:

123456789

but in the talbe I see it as:  123-45-6789

I want the ComboBox to show it as:  123-45-6789   As I type the data.

I know I used StrConv to combine two fields that are strings.  Since the Data is in a String, I thought I could use this to show th data as I type in that format?

What I have is simply what I have done with a Driver's Info.  I have a ComboBox populated with data from the table, and if the data is not in list then I have a form come up and add the data to the table and refresh the ComboBox...but it doesn't come up as in the correct format if I don't use an Input mask.  

If I use the Input Mask all is OK, but the data doesn't come up as they type.  I wanted to keep that but format the data to show up correctly as they type.

So a user types:  123456789  
User is asked to add data
user Adds data
Goes back to Form  but data is not saved (*or shows in ComboBox as a string but in table in correct format)

I want the combobox to show in correct format

Is there a way to do this?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Base your combo on a query that formats the field.  The combo will then display ###-##-#### and you will match that as you type.

Jim.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Ernest GroggSecurity Management InfoSecAuthor Commented:
Well, I don't know if I am doing something wrong but...2 examples.  One shows data entered on another form as a new customer, but data entered on this form through the ComboBox is saved in ComboBox in a string.

Table shows as correct format...

Untitled.pngUntitled1.png
Jeffrey CoachmanMIS LiasonCommented:
Try stetting the Input Mask Property of he combobox to:
000\-00\-0000;;_


JeffCoachman
Jeffrey CoachmanMIS LiasonCommented:
...or set the Format property of he combobox to:
@@@-@@-@@@@

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
The properties of the combo box control how the bound field is displayed.  They do not control how the RowSource is displayed and that is what you need to control.  To do what you are asking requires TWO settings.
1. The Input Mask for the combo must be 000\-00\-0000;;_ as has already been suggested.
PLUS
2. The RowSource query must have a format property of @@@-@@-@@@@

If you don't set the property on the RowSource query, the dropdown list will not show the dashes.  If you don't set the input mask property on the combo itself, Access will think you want to store the dashes.

To ensure that the data is being saved correctly (without the dashes), remove any formatting or input mask from the table.  That way you can see what is actually being saved.  Also limit the field length to 9 digits so there is no room for the dashes.
Ernest GroggSecurity Management InfoSecAuthor Commented:
OK...sorry I have not responded.

Been working on it and got it perfectly.

Maybe I should file this under a new question but here it is and if so, let me know,

When the user types the numbers in, and it is less than or more than the 9 digits, I get the box:  Select an Item from the list...
How can I get the msgbox I have to show?

Here is me Code for the NotInListEvent  (I changed it to add directly to the table and not open a form)

On Error GoTo myError
    
    Dim rst As DAO.Recordset
        
    Set rst = CurrentDb.OpenRecordset("Table", dbOpenDynaset)
    
        If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
                & NewData & " as a Field?", _
                vbYesNoCancel + vbDefaultButton2, _
                "New SSN") Then

            If Len(Form_Form_1.cboField) <> 9 Then
                MsgBox "You must enter a 9 digits for the Field", vbOKOnly
                Cancel = True
                Response = acDataErrContinue
                Exit Sub
            
    
            rst.AddNew
                rst!SSN = NewData
            rst.Update
            
            Response = acDataErrAdded
                Me.PersonID = DLookup("[PersonID]", "Table", _
             "[Field]='" & NewData & "'")
            End If
        Else
        
            Response = acDataErrContinue
            
        End If
       
leave:

    If Not rst Is Nothing Then
        rst.Close: Set rst = Nothing
    End If
    
    Exit Sub
    
myError:
   
    MsgBox "Error " & Err.Number & ": " & "The field can only contain 9 digets" & vbCrLf & _
        "Please check your numbers and try again."
    Response = acDataErrContinue
    Resume leave

Open in new window


Thoughts?
PatHartmanCommented:
Why would you allow an SSN to be entered without supporting information?

Also, are you aware that using SSN as a primary key is not allowed since it makes the SSN more vulnerable to identity theft?  SSN is supposed to be a reference number only.  It should be hidden in the table so that only authorized users can even see it.
Ernest GroggSecurity Management InfoSecAuthor Commented:
The,supporting information is in a subform in which I changed the process to accommodate the regulations for PII.  Plus this is not a primary key, it is part of supporting information that another dept is using.   Besides all regulations for access to these sensitive areas are taken care of by that side of the house.  In other words, the federal regulations have been adhered to.  

Maybe we can discuss offline if u wish?  I would be open.  

Is there something that can be done?
PatHartmanCommented:
Ernest,
You are adding a row to a table and that row contains ONLY SSN.  How does that make sense?  "The,supporting information is in a subform" is not connected to the record you are inserting especially since the primary key of the two records must be different.

Have you tried using the Change event to count characters?  Using an Input Mask interferes with normal processing and that may be the reason that you are not trapping the message.  Is your NotInList event firing?  Does it fire before or after you get the error message.
Jeffrey CoachmanMIS LiasonCommented:
I am confused.
Has the question here changed...?
I posted possible solutions:
http://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html#a40851384
...and:
http://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html#a40851388

, ...but you did not reply if it worked for you or not...

JeffCoachman
PatHartmanCommented:
@Jeff, It does appear that the question has changed but just FYI, refer to my post http://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html#a40853276 for the solution to how to actually get the RowSource to display the formatting and not complain about the dashes when saving the record.
Ernest GroggSecurity Management InfoSecAuthor Commented:
The solution does work, I was asking about something related to the original but that's ok.....

I really appreciate the help

Have a great day!
Jeffrey CoachmanMIS LiasonCommented:
@ Ernest and Pat
To be clear,...
I just wanted to know if my solution worked.

I had no problem with Pat helping you with the follow up question.
I would have gladly split the points.

If you wish to reopen the Q, I have no problem.
;-)

Jeff
PatHartmanCommented:
But Jeff,
Setting the Format of the combo box to @@@-@@-@@@@ was not the solution.
Jeffrey CoachmanMIS LiasonCommented:
Pat,
hmmm.

Ok then, I guess the Q can be re-opened.

Perhaps I did not understand fully.

I will step aside (to avoid confusion) and let you work with the OP to conclusion.

Jeff
PatHartmanCommented:
I don't care about the points but the key to getting this to work was two settings.  One for the RowSource and another for the control itself.  Setting properties on the control has no impact on what shows in the list.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.