StrConv to display as SSN

Not sure I understand the STRCONV.  You just need a format specified:

###-##-####

Jim.

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?

Base your combo on a query that formats the field.  The combo will then display ###-##-#### and you will match that as you type.

Jim.

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...

Try stetting the Input Mask Property of he combobox to:
000\-00\-0000;;_


JeffCoachman

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

Select allOpen in new window

Thoughts?

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.

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?

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.

I am confused.
Has the question here changed...?
I posted possible solutions:
https://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html?anchorAnswerId=40851384#a40851384
...and:
https://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html?anchorAnswerId=40851388#a40851388

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

JeffCoachman

@Jeff, It does appear that the question has changed but just FYI, refer to my post https://www.experts-exchange.com/questions/28692435/StrConv-to-display-as-SSN.html?anchorAnswerId=40853276#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.

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!

@ 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

But Jeff,
Setting the Format of the combo box to @@@-@@-@@@@ was not the solution.

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

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.