Mask a field in a form

[travismp]

Access 2000
Table tbl_TEMP
Table tbl_TESTS
Query qry_TESTS
Form frm_TESTS

There are 10 records in tbl_TEMP. Form frm_TESTS shows each record. My user makes changes to the record and clicks an “Accept” button, which excuses a VB script that appends data from tbl_TEMP into tbl_TESTS. That all works perfect.

There is a general text field in tbl_TEMP that will become a Social Security masked field once transferred to tbl_TESTS. Right now if someone attempts to accept the test and the [ssn] field is not truly in the proper mask of xxx-xx-xxxx then it will look as if it is appending the test, but it truly does not copy the record.

Can I add a warning somewhere that makes them change any text in that field to the proper mask?

So ex: if they see “123456” in the [ssn] field in the form they must change it to “123-45-6000” or any other system they choose to fill it in, but make it where they much change it before they can “Accept” the test?

Thanks.

 

[Moniker]

Go to that table's design view and on the field you want to contain the SSN, put this in the Input Mask property:

000\-00\-0000;_

The underscore after the semicolon is the placeholder (what they will see when typing into that field). The backslashes ( \ ) mean the next character is a literal and not an arithmetic operation.

~Moniker

 

[travismp]

tbl_TEMP is just general text
tbl_TESTS has the mask.

I cannot control the data from tbl_TEMP. It is coming from an outside source. I cannot accept it into the system until it is in the proper ss format. So is there anything I can add to the form that looks to make sure that the mask of xxx-xx-xxxx has been applied to any data in the [ssn] field to ensure it copies over to tbl_TESTS?

 

[boblarson]

You could put this in the click event of the button or the Before Update event of the form:

    Me.YourTextBoxName = Format(Me.YourTextBoxName, "000-00-0000")

 

[travismp]

Big Bob Larson once again coming to the assistance...

Private Sub cmdAccept_Click()
    On Error GoTo err_handler
    If Me.chkCOMPLETE Then
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery "qry_ESKER_SEND_DATA_TO_TESTS_JUSTIN", acViewNormal
    Me.Requery
    DoCmd.SetWarnings (True)
    Else
        MsgBox "You cannot transfer this record until the COMPLETE" & vbCrLf & _
        "checkbox has been checked.", vbExclamation, "Data Entry Error!"
    End If
    Exit Sub
err_handler:
    DoCmd.SetWarnings (True)
    MsgBox Err.Description, vbExclamation, "Error Number: " & Err.Number
    Exit Sub
End Sub

So where can I sneak that in Bob? Thanks. I think what you have will work, but I do not know where to insert it.

I tried

If Me.chkCOMPLETE And Me.SSN = Format(Me.SSN, "000-00-0000") Then

But that did not work. Any help would be great. thanks.

 

[boblarson]

Did you say that this comes to you via an outside source? Does it just need to be formatted? Or is it possible that the SSN will not be able to be formatted in a correct way.

If it comes to you with all valid numbers then you SHOULD be able to use:

If Me.chkCOMPLETE Then
Me.SSN = Format(Me.SSN, "000-00-0000") 
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings (False)
... (abbreviated for space)

 

[travismp]

It comes in mostly correct, but can be a simple 5 digit number at times. Those few times it is not correct my user needs to change it to a correct format. I will try this to see if it will work. I will let you know.

 

[Moniker]

You can't check a format with an IF statement like that. You'll have to check the format manually, which is sort of a pain. Something like this will confirm the SSN structure:

If Mid(Me.SSN,4,1) = "-" and Mid(Me.SSN,7,1) = "-" Then ...

That would confirm the dashes in "123-45-6789".

You could also use something to just convert the entire thing to a number (using Val() will fail at the first dash for this). This will convert the entire string into a number:

Function NumbersOnly(strConvert As String) As String
'Returns only the numbers from a passed string. 

    Dim curChar As String
    Dim ctr As Integer
 
    If IsNull(strConvert) Then
        NumbersOnly = ""
        Exit Function
    End If
    
    For ctr = 1 To Len(strConvert)
        curChar = Mid(strConvert, ctr, 1)
        If IsNumeric(curChar) Then
            NumbersOnly = NumbersOnly & curChar
        End If
    Next
    
End Function

You could then use it like Me.SSN = NumbersOnly(Me.SSN).

The best solution is to put a mask on the temp table, but it sounds like you can't do that. Really, though, you don't want to store the dashes with the SSN in the first place. That's something you do for display. It's the same way you should store a phone number as 1234567890 and not 123-456-7890.

~Moniker

 

[travismp]

Nope did not work. I changed the vb. Found a record that had a 5 digit number I hit the "Accept" button and it acted like the test went through. No errors, no messages. It just brings up the next record. Of course the test does not transfer across.

Any other thoughts? Thanks.

 

[Moniker]

Have you considered a test like this:

If len(Me.SSN) < 9 Then
--- error ---
Else
--- attempt to process ---


~Moniker

 

[travismp]

Moniker & Bob THANK YOU for the help.

Moniker I went this route:
If Mid(Me.SSN,4,1) = "-" and Mid(Me.SSN,7,1) = "-" Then...

And it worked great. I can see where ther could be a couple small issues like if they forget a number becuase it is only looking for the mask, but this will catch 99% of them. Thanks for the help tonight.

Travis