rae said:I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUMrae said:I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
:
I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUM
The exact code is:
Private Sub SSN_LostFocus()
Dim strssn As String
strssn = "xxx-xx-" & Right([SSN], 4)
Me!SSN = strssn
End Sub
Problem is that the data is sent to the table as xxx-xx-66789.
I need the whole number.
J. Goddard said:Hi -
It's because your SSN control on the form is bound to the SSN field in
the table, so when you change it with the mask, that's what goes to the
database table.
Try putting in an additional control for the real SSN, binding it to the
table field, and make it invisible on the form. Then make the masked
one and unbound text box, with the masked value. I gather what you are
doing is masking the value immediately after the user enters it, so your
code could be (in the Masked, data entry box lost focus)
Private Sub MaskedSSN_LostFocus()
Dim strssn As String
'
' Copy real SSN to invisible text box
'
Me!SSN = me![maskedssn]
strssn = "xxx-xx-" & Right([SSN], 4)
me![MaskedSSN] = strssn
End Sub
Hope this helps
John
:
I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUM
The exact code is:
Private Sub SSN_LostFocus()
Dim strssn As String
strssn = "xxx-xx-" & Right([SSN], 4)
Me!SSN = strssn
End Sub
Problem is that the data is sent to the table as xxx-xx-66789.
I need the whole number.
HI!
Great suggestion. The only situation is that the ssn now populates every
single ssn txt box on the form. What can I do to stop this?
:
Hi -
It's because your SSN control on the form is bound to the SSN field in
the table, so when you change it with the mask, that's what goes to the
database table.
Try putting in an additional control for the real SSN, binding it to the
table field, and make it invisible on the form. Then make the masked
one and unbound text box, with the masked value. I gather what you are
doing is masking the value immediately after the user enters it, so your
code could be (in the Masked, data entry box lost focus)
Private Sub MaskedSSN_LostFocus()
Dim strssn As String
'
' Copy real SSN to invisible text box
'
Me!SSN = me![maskedssn]
strssn = "xxx-xx-" & Right([SSN], 4)
me![MaskedSSN] = strssn
End Sub
Hope this helps
John
:
I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUM
The exact code is:
Private Sub SSN_LostFocus()
Dim strssn As String
strssn = "xxx-xx-" & Right([SSN], 4)
Me!SSN = strssn
End Sub
Problem is that the data is sent to the table as xxx-xx-66789.
I need the whole number.
J. Goddard said:Hi -
What do you mean by "every single text box on the form"? If your form's
"default view" is set to "continuous forms" rather than "single form",
you might get that, but it should only be in the unbound masked text box
(I can't be sure, though - I don't use continuous view). MVP's ??
If your view is set to "single form", the only reason I can think of is
that you have more than one text box bound to the same table field -
which to me makes no sense at all.
Hope this gets you pointed in the right direction
John
HI!
Great suggestion. The only situation is that the ssn now populates every
single ssn txt box on the form. What can I do to stop this?
:
Hi -
It's because your SSN control on the form is bound to the SSN field in
the table, so when you change it with the mask, that's what goes to the
database table.
Try putting in an additional control for the real SSN, binding it to the
table field, and make it invisible on the form. Then make the masked
one and unbound text box, with the masked value. I gather what you are
doing is masking the value immediately after the user enters it, so your
code could be (in the Masked, data entry box lost focus)
Private Sub MaskedSSN_LostFocus()
Dim strssn As String
'
' Copy real SSN to invisible text box
'
Me!SSN = me![maskedssn]
strssn = "xxx-xx-" & Right([SSN], 4)
me![MaskedSSN] = strssn
End Sub
Hope this helps
John
rae wrote:
:
I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUM
The exact code is:
Private Sub SSN_LostFocus()
Dim strssn As String
strssn = "xxx-xx-" & Right([SSN], 4)
Me!SSN = strssn
End Sub
Problem is that the data is sent to the table as xxx-xx-66789.
I need the whole number.
What I mean is that every ssn txt box is filled with the current ssn being
entered. Yes, it's not my choice, but I am using a continuous form. I have no
other field bound to the ssn part of the table, infact, none of the fields
'share' a table section.
So, I'm curious if there was something I missed? Your code seems to work
just fine, however, I wonder if I missed a toggle at some point?
Rae
:
Hi -
What do you mean by "every single text box on the form"? If your form's
"default view" is set to "continuous forms" rather than "single form",
you might get that, but it should only be in the unbound masked text box
(I can't be sure, though - I don't use continuous view). MVP's ??
If your view is set to "single form", the only reason I can think of is
that you have more than one text box bound to the same table field -
which to me makes no sense at all.
Hope this gets you pointed in the right direction
John
HI!
Great suggestion. The only situation is that the ssn now populates every
single ssn txt box on the form. What can I do to stop this?
:
Hi -
It's because your SSN control on the form is bound to the SSN field in
the table, so when you change it with the mask, that's what goes to the
database table.
Try putting in an additional control for the real SSN, binding it to the
table field, and make it invisible on the form. Then make the masked
one and unbound text box, with the masked value. I gather what you are
doing is masking the value immediately after the user enters it, so your
code could be (in the Masked, data entry box lost focus)
Private Sub MaskedSSN_LostFocus()
Dim strssn As String
'
' Copy real SSN to invisible text box
'
Me!SSN = me![maskedssn]
strssn = "xxx-xx-" & Right([SSN], 4)
me![MaskedSSN] = strssn
End Sub
Hope this helps
John
rae wrote:
:
I need to mask out the first five numbers of a ssn entered into a form. I have
([SSN]),"","xxx-xx-" & Right([SSN],4) as the lost focus event.
it doesn't work. What am I doing wrong?
(I am brand new to all of this)
ADDENDUM
The exact code is:
Private Sub SSN_LostFocus()
Dim strssn As String
strssn = "xxx-xx-" & Right([SSN], 4)
Me!SSN = strssn
End Sub
Problem is that the data is sent to the table as xxx-xx-66789.
I need the whole number.
rae said:Hi
I think I'm a bit flustered by all of this. I want to get it right
before I mess with it too much.
I should make a query that has all the fields that is on my form and
tack another in for the MaskedSSN. Confused by this:
"MaskedSSN:"xxx-xx-" & Right([SSN], MaskedSSN is the field name in
the query." how do I write this into the query?
Also, if I do this: " Set the control source of the masked SSN
control to the MaskedSSN field of the query, set the locked property
to Yes, and set Tab Stop to No. " Won't I lose the ssn number to the
query and it not populate the table?
Rick Brandt said:rae said:Hi
I think I'm a bit flustered by all of this. I want to get it right
before I mess with it too much.
I should make a query that has all the fields that is on my form and
tack another in for the MaskedSSN. Confused by this:
"MaskedSSN:"xxx-xx-" & Right([SSN], MaskedSSN is the field name in
the query." how do I write this into the query?
You can add fields to queries based on expressions by simply typing a field name
followed by a colon followed by the expression. For example...
MaskedSSN: "xxx-xx-" & Right([SSN], 4)
Also, if I do this: " Set the control source of the masked SSN
control to the MaskedSSN field of the query, set the locked property
to Yes, and set Tab Stop to No. " Won't I lose the ssn number to the
query and it not populate the table?
Since the field is based on an expression it will not be editable anyaway. No
need to lock it except to prevent the message that the user would get should
they try to edit it.
I don't understand the last question. The MaskedSSN field ONLY exists as an
expression in the query for *display* purposes. It does not exist in your
table. Only the SSN field on which the expression is based does.
rae said:Hi Rick,
Thanks so much. So I wrote a simple query to list all the fields on my
form...I put in the following:
SELECT Table1.Appt_Date, Table1.[Customers First Name],
Table1.[Customers Last Name], MaskedSSN: "xxx-xx-" &
Right([Table1.SSN], 4), Table1.Program, Table1.Service, Table1.Team,
Table1.Worker, Table1.Appointment_Time, Table1.[Check In],
Table1.[Check Out]
FROM Table1;
I am getting an error msg saying that I am missing an operator. What
could I be doing wrong?
Rick Brandt said:rae said:Hi Rick,
Thanks so much. So I wrote a simple query to list all the fields on my
form...I put in the following:
SELECT Table1.Appt_Date, Table1.[Customers First Name],
Table1.[Customers Last Name], MaskedSSN: "xxx-xx-" &
Right([Table1.SSN], 4), Table1.Program, Table1.Service, Table1.Team,
Table1.Worker, Table1.Appointment_Time, Table1.[Check In],
Table1.[Check Out]
FROM Table1;
I am getting an error msg saying that I am missing an operator. What
could I be doing wrong?
The syntax of...MaskedSSN: "xxx-xx-" & Right([Table1.SSN], 4)
....would be used in the query design grid, not in SQL. In SQL it would look
like...
"xxx-xx-" & Right([Table1.SSN], 4) AS MaskedSSN,
Hi
I think I'm a bit flustered by all of this. I want to get it right before I
mess with it too much.
I should make a query that has all the fields that is on my form and tack
another in for the MaskedSSN. Confused by this: "MaskedSSN:"xxx-xx-" &
Right([SSN], MaskedSSN is the field name in the query." how do I write
this into the query?
Also, if I do this: " Set the control source of the masked SSN control to
the MaskedSSN field of the query, set the locked property to Yes, and set Tab
Stop to No. " Won't I lose the ssn number to the query and it not populate
the table?
Thanks,
Rae
:
Hi -
I was able replicate the behaviour you are seeing - it is the unbound
text box that is being replicated in all instances of the form. Any
changes I make to the bound controls (using the after update event of
the unbound box) are made only in the current record, i.e. the one the
cursor is in; but every time I change the unbound box, the changes are
made in all instances of the form.
With a little experimentation, I have a solution.
First:
Create a query containing all the fields from the table you are using,
plus an extra calculated field that masks the SSN:
MaskedSSN:"xxx-xx-" & Right([SSN], 4) MaskedSSN is the field name in
the query.
Next, change the record source of your form to the query, instead of the
table.
Make the SSN field on the form visible again, and set the inputmask
property to "Password" (no quotes)
Set the control source of the masked SSN control to the MaskedSSN field
of the query, set the locked property to Yes, and set Tab Stop to No.
Take the code to set the mask out of the On Lost Focus event of the SSN
- you don't need it because the query does it.
Give this a try.
John
J. Goddard said:Hi -
First part-
What you are doing in the query I suggested is create a field which is
an expression that is based on other field(s) in the query, or the
table(s) on which the query is based.
In your case, the expression is based on one field, SSN, from one table.
Open the query in design view, and in a blank column, type this in the
"Field:" line (instead of a table field name):
MaskedSSN:"xxx-xx-" & Right([SSN],4)
The expression is the one you gave way back in the first post for the
masked SSN.
The 'MaskedSSN:' part is actually optional; what it does is give a
meaningful column header when you run the query. If you don't include
it, MS Access uses the default column header, 'Expr1'.
Now, because this is a Select query, it does not *change* anything in
the SSN field - it just displays it differently.
second Part:
I think what you are asking is - "Can a select query be used to update a
table?" The answer is yes, much of the time they can, and it is true
here. Basing your form on the query is the same as basing it on the table.
Your form will contain a control where you will type in the real SSN,
and it is bound to the SSN field in the query, which in turn is the SSN
field in the table. The Input Mask 'password' causes it to display as
'*********' on the screen, the value added to the table is the real SSN.
Another control on the form is bound to the MaskedSSN field in the
query, so it *displays* the masked version of the SSN, but because the
query field is an expression, it does not update the table. The form
control is automatically filled in by the query - you don't need to
change it.
Cheers!
John
Hi
I think I'm a bit flustered by all of this. I want to get it right before I
mess with it too much.
I should make a query that has all the fields that is on my form and tack
another in for the MaskedSSN. Confused by this: "MaskedSSN:"xxx-xx-" &
Right([SSN], MaskedSSN is the field name in the query." how do I write
this into the query?
Also, if I do this: " Set the control source of the masked SSN control to
the MaskedSSN field of the query, set the locked property to Yes, and set Tab
Stop to No. " Won't I lose the ssn number to the query and it not populate
the table?
Thanks,
Rae
:
Hi -
I was able replicate the behaviour you are seeing - it is the unbound
text box that is being replicated in all instances of the form. Any
changes I make to the bound controls (using the after update event of
the unbound box) are made only in the current record, i.e. the one the
cursor is in; but every time I change the unbound box, the changes are
made in all instances of the form.
With a little experimentation, I have a solution.
First:
Create a query containing all the fields from the table you are using,
plus an extra calculated field that masks the SSN:
MaskedSSN:"xxx-xx-" & Right([SSN], 4) MaskedSSN is the field name in
the query.
Next, change the record source of your form to the query, instead of the
table.
Make the SSN field on the form visible again, and set the inputmask
property to "Password" (no quotes)
Set the control source of the masked SSN control to the MaskedSSN field
of the query, set the locked property to Yes, and set Tab Stop to No.
Take the code to set the mask out of the On Lost Focus event of the SSN
- you don't need it because the query does it.
Give this a try.
John
John~
You rock. Thanks so much for all your insight. I was able to replicate what
you suggested and it is working like a charm.
I would be so lost without this forum~
Thanks to you and Rick both
Rae
:
Hi -
First part-
What you are doing in the query I suggested is create a field which is
an expression that is based on other field(s) in the query, or the
table(s) on which the query is based.
In your case, the expression is based on one field, SSN, from one table.
Open the query in design view, and in a blank column, type this in the
"Field:" line (instead of a table field name):
MaskedSSN:"xxx-xx-" & Right([SSN],4)
The expression is the one you gave way back in the first post for the
masked SSN.
The 'MaskedSSN:' part is actually optional; what it does is give a
meaningful column header when you run the query. If you don't include
it, MS Access uses the default column header, 'Expr1'.
Now, because this is a Select query, it does not *change* anything in
the SSN field - it just displays it differently.
second Part:
I think what you are asking is - "Can a select query be used to update a
table?" The answer is yes, much of the time they can, and it is true
here. Basing your form on the query is the same as basing it on the table.
Your form will contain a control where you will type in the real SSN,
and it is bound to the SSN field in the query, which in turn is the SSN
field in the table. The Input Mask 'password' causes it to display as
'*********' on the screen, the value added to the table is the real SSN.
Another control on the form is bound to the MaskedSSN field in the
query, so it *displays* the masked version of the SSN, but because the
query field is an expression, it does not update the table. The form
control is automatically filled in by the query - you don't need to
change it.
Cheers!
John
Hi
I think I'm a bit flustered by all of this. I want to get it right before I
mess with it too much.
I should make a query that has all the fields that is on my form and tack
another in for the MaskedSSN. Confused by this: "MaskedSSN:"xxx-xx-" &
Right([SSN], MaskedSSN is the field name in the query." how do I write
this into the query?
Also, if I do this: " Set the control source of the masked SSN control to
the MaskedSSN field of the query, set the locked property to Yes, and set Tab
Stop to No. " Won't I lose the ssn number to the query and it not populate
the table?
Thanks,
Rae
:
Hi -
I was able replicate the behaviour you are seeing - it is the unbound
text box that is being replicated in all instances of the form. Any
changes I make to the bound controls (using the after update event of
the unbound box) are made only in the current record, i.e. the one the
cursor is in; but every time I change the unbound box, the changes are
made in all instances of the form.
With a little experimentation, I have a solution.
First:
Create a query containing all the fields from the table you are using,
plus an extra calculated field that masks the SSN:
MaskedSSN:"xxx-xx-" & Right([SSN], 4) MaskedSSN is the field name in
the query.
Next, change the record source of your form to the query, instead of the
table.
Make the SSN field on the form visible again, and set the inputmask
property to "Password" (no quotes)
Set the control source of the masked SSN control to the MaskedSSN field
of the query, set the locked property to Yes, and set Tab Stop to No.
Take the code to set the mask out of the On Lost Focus event of the SSN
- you don't need it because the query does it.
Give this a try.
John