value of a field with an input mask?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 26 Jan 2008 23:33 GMT

If I have a combobox for a phone number, and i limit the input of the field
using the input mask (000)000-000;;_

What is the value of the field of the combo box when you type in the number
1234567890? Would it be

A. 1234567890

or

B. (123)456-7890

My understanding is that it would be A. However, the code I have in the
After Update event seems to be seeing it with the formatting attached to it.
The code in AfterUpate looks to see if that phone number exists. If it
exists, it goes to that record. If it does not exist, it goes to the
NotInList event, which asks you if you want to create a new record based on
that number. Using the input masked mentioned in the beginning, No number i
type catches as existing in the table at all. Even when I verify it with my
eyes in the table. If I use an input mask of 0000000000;;_ or simply remove
the input mask altogether, it will find the number that gets typed in.

Correct me if i'm wrong, but unless you set the input mask to save the mask
into the value, it's just a visual place holder isn't it? It shouldn't be
part of the value of the field, right?

What makes this more frustrating is that as I set the database up, the code
was working properly with the input mask being used. The only thing that has
changed is that I imported my old database information into this new one so
it's dealing with few thousand records instead of 10. Is there any logical
reason for the change of behaviour?

Reply to this Message

Ken Snell (MVP) - 28 Jan 2008 00:49 GMT

> If I have a combobox for a phone number, and i limit the input of the
> field using the input mask (000)000-000;;_

Assuming that you're wanting a Mask for US telephone numbers, you're missing
a zero at the end of the first argument.

> What is the value of the field of the combo box when you type in the
> number 1234567890? Would it be
[quoted text clipped - 4 lines]
>
> B. (123)456-7890

Assuming that the Input Mask is a property of the combo box control in the
form, the answer is A. Is this where you're setting the Input Mask? Or are
you doing it in the table's field's property?

> My understanding is that it would be A. However, the code I have in the
> After Update event seems to be seeing it with the formatting attached to
> it.

Post the code that you're using.

> The code in AfterUpate looks to see if that phone number exists. If it
> exists, it goes to that record. If it does not exist, it goes to the
[quoted text clipped - 14 lines]
> new one so it's dealing with few thousand records instead of 10. Is there
> any logical reason for the change of behaviour?

Check the imported data. Did the old data have the input mask characters
stored with the numbers?

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message

Mike - 28 Jan 2008 03:37 GMT

Comments inline.

>> If I have a combobox for a phone number, and i limit the input of the
>> field using the input mask (000)000-000;;_
>
> Assuming that you're wanting a Mask for US telephone numbers, you're
> missing a zero at the end of the first argument.

That would be a typo in the post. The mask is (000)000-0000;;_

>> What is the value of the field of the combo box when you type in the
>> number 1234567890? Would it be
[quoted text clipped - 8 lines]
> form, the answer is A. Is this where you're setting the Input Mask? Or are
> you doing it in the table's field's property?

No masking in the table itself, only the combo box.

>> My understanding is that it would be A. However, the code I have in the
>> After Update event seems to be seeing it with the formatting attached to
>> it.
>
> Post the code that you're using.

Here's the code:

With Me.RecordsetClone
.FindFirst "[ID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

>> The code in AfterUpate looks to see if that phone number exists. If it
>> exists, it goes to that record. If it does not exist, it goes to the
[quoted text clipped - 17 lines]
> Check the imported data. Did the old data have the input mask characters
> stored with the numbers?

There was no formatting on the old data. It's something that bugged me for
ages and I finally got around to re-writing the database from scratch. The
previous field was full of different manual formats: 123-456-7890,
123-456-7890x1234 (for extentions), (123)456-789, blanks, dashes for
non-existant entries, etc. I kicked out the old DB into excel and went
through all the entries and stripped them down to 10 digits in all cases.
Pushed extensions into a new field, manually went through and removed any
type of formatting so there were just 10 digits. After importing it into the
new database, the field was reset to just 10 characters in the table setup
itself. It didn't rewrite any values. I've scrolled the entire list of
numbers in the table and they're all 10 digits. The field values itself are
fairly basic. There is no format set, there is no mask set (the mask was
only set in the combo box properties), and the field type is set to text. At
one point the field was set to be required, but I don't know if that's set
presently or not.

Maybe i should verify it by running a query for string length? If length is
not equal to 10 or something and see if any records come up. Or if there are
any records with non numerical characters? Don't know how to do that
one...is that doable? Either way though, that shouldn't mess up the code
looking to see if the value already exists should it?

The values stored in the table are in the form of 1234567890.

If i type in 1234567890 without an input mask (or with the mask
0000000000;;_), the combo box code above finds the record. No problem.

If i set the input mask field of the combo box to (000)000-0000;;_ it
doesn't find it. Leads me to believe it's searching with the input mask. I
suppose it could be something else, but i have no clue what it could be.

Reply to this Message

Ken Snell (MVP) - 29 Jan 2008 03:37 GMT

>>> What is the value of the field of the combo box when you type in the
>>> number 1234567890? Would it be
[quoted text clipped - 10 lines]
>
> No masking in the table itself, only the combo box.

OK; that's good.

>>> My understanding is that it would be A. However, the code I have in the
>>> After Update event seems to be seeing it with the formatting attached to
[quoted text clipped - 10 lines]
> End If
> End With

Put a breakpoint on the "FindFirst" step. When the code stops, put your
cursor over the Me.MyCombo part of the code step. A "control tip text box"
will pop up and show you the value of that combo box. What do you see as the
value?

What is the data type of the ID field -- text? numeric? If it's text, the
code step should be this:
.FindFirst "[ID] = '" & Me.MyCombo & "'"

>>> The code in AfterUpate looks to see if that phone number exists. If it
>>> exists, it goes to that record. If it does not exist, it goes to the
[quoted text clipped - 8 lines]
>>> mask into the value, it's just a visual place holder isn't it? It
>>> shouldn't be part of the value of the field, right?

I tested the presence of the Input Mask in a combo box, and could not
reproduce the behavior that it clearly appears you are seeing. It's possible
that the combo box has some corruption in it. Try deleting the combo box and
recreating it on the form. Also, consider using the "input mask" format in
the Format property of the combo box, not the Input Mask. If you're using
the Input Mask to control the "format" of the new values that the user
enters, you may want to use a separate form (opened by the NotInList event
(when the Limit To List property is set to Yes) for the user to enter new
value. Then you could use the Input Mask there to control what the user
types.

Or you could use the approach shown here:
http://www.mvps.org/access/forms/frm0015.htm

>>> What makes this more frustrating is that as I set the database up, the
>>> code was working properly with the input mask being used. The only thing
>>> that has changed is that I imported my old database information into
>>> this new one so it's dealing with few thousand records instead of 10. Is
>>> there any logical reason for the change of behaviour?

Nothing that is obvious to me based on the information that you've provided
so far.

>> Check the imported data. Did the old data have the input mask characters
>> stored with the numbers?
[quoted text clipped - 17 lines]
> Maybe i should verify it by running a query for string length? If length
> is not equal to 10 or something and see if any records come up.

Yes, I would agree with this being done.

Or if there are

> any records with non numerical characters? Don't know how to do that
> one...is that doable?

You could use a query like this:

SELECT *
FROM Tablename
WHERE IsNumeric(ID) = False;

> Either way though, that shouldn't mess up the code looking to see if the
> value already exists should it?

No.

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message

Mike - 29 Jan 2008 17:37 GMT

This is interesting...

I did the breakpoint AND added a Watch event.

If I use a value from the pull down list in the combo box, the Stop event
works and the number displays properly and the watch picks up the 10 digit
value passed onto the arguement.

Here's where it's interesting - to me at least.....

The breakpoint has no effect if I type a number into the \(000\)000\-0000
input mask form. It seems as if it's skipping the after_update code
altogether and it jumps to the NotInList event. It tells me the number isn't
in the list and asks me if I want to create a new record (that code is in
the NotInList event). The watch event picks up nothing at all.

It would seem the input mask is somehow making it skip the AfterUpdate event
altogether.

I restarted the db a couple times. Only pulling a value from the pull down
portion of the combo trips the breakpoint and watch list, otherwise it skips
down to the NotInList event automatically. If I take out the input mask, it
works correctly.

Oh, the field in questions is actually Phone, not ID, and it is a Text
field. That was the sample code. The code as written in the db is:

With Me.RecordsetClone
.FindFirst "[Phone] = " & Me.LookPhone
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

However, the code seems to be skipped when the standard phone input mask is
in place. It's not that the value isn't matching, its being skipped it
seems.

Mike

>>>> What is the value of the field of the combo box when you type in the
>>>> number 1234567890? Would it be
[quoted text clipped - 111 lines]
>
> No.

Reply to this Message

Mike - 29 Jan 2008 18:24 GMT

More information for you...

As is, the Limit To List field is set to YES. With this set, I can't lookup
records using the input mask.

I rebuilt the control and thought it worked (it looked up values with the
input mask present). However, it no longer was calling the NotInList event.
This is because, on rebuilding the control, by default, the Limit To List
field is set to NO.

So, with Limit to List set to YES:
Lookup works with no input mask or an input mask of 0000000000;;_
NotInList is called and works correctly.

With Limit to List set to NO:
Lookup works correctly with the input mask \(000\)000\-000;;0
NotInList is not called at all.

I do understand the part about why NotInList only works when limit to list
is set to YES. I don't understand how it affects the AfterUpdate portion of
things to the point that it doesn't work with standard phone mask.

> This is interesting...
>
[quoted text clipped - 150 lines]
>>
>> No.

Reply to this Message

Ken Snell (MVP) - 30 Jan 2008 03:21 GMT

The problem appears to be in the NotInList event; it sees the "NewData"
variable, which likely is the .Text property of the combo box, and not the
.Value property. The .Text property is what is displayed in the combo box,
which will include the input mask. So your observations now make sense to
me, because the NotInList code likely is "keeping" the input mask characters
in the data -- do you see the input mask characters in the new data in the
table after you've stored the new value?

You haven't posted your code for the NotInList event, so I am just guessing
here as to what might be that code. But, if you use a separate form to let
the user type in the new value, instead of using the code at the
www.mvps.org/access site, then you can let the new value be entered by the
input mask, and still store the data if the combo box is bound to the field
in the table.

Otherwise, you may need to strip out the non-numeric characters from the
"NewData" variable before you save the new value into the table.

Signature

Ken Snell
<MS ACCESS MVP>

> More information for you...
>
[quoted text clipped - 174 lines]
>>>
>>> No.

Reply to this Message

Mike - 30 Jan 2008 16:51 GMT

> The problem appears to be in the NotInList event; it sees the "NewData"
> variable, which likely is the .Text property of the combo box, and not the
[quoted text clipped - 3 lines]
> characters in the data -- do you see the input mask characters in the new
> data in the table after you've stored the new value?

The characters are not seen in the table. Just 1234567890.

I must be missing something because from the break point, it seemed that the
AfterUpdate code wasn't being used at all when the input mask was present.
The code did not trip on that line and jumped right to the notinlist event.

> You haven't posted your code for the NotInList event, so I am just
> guessing here as to what might be that code. But, if you use a separate
> form to let the user type in the new value, instead of using the code at
> the www.mvps.org/access site, then you can let the new value be entered by
> the input mask, and still store the data if the combo box is bound to the
> field in the table.

The NotInList code:

Private Sub LookPhone_NotInList(NewData As String, Response As Integer)

If MsgBox(NewData & " Is not in the database " & vbNewLine _
& "Do you want to Create a new record?", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.LookPhone = Me.LookPhone.OldValue
CurrentDb.Execute ("INSERT INTO Customers ([Phone]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
.FindFirst "[Phone] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me.LookPhone.Undo
Response = acDataErrContinue
End If

End Sub

> Otherwise, you may need to strip out the non-numeric characters from the
> "NewData" variable before you save the new value into the table.
[quoted text clipped - 178 lines]
>>>>
>>>> No.

Reply to this Message

Ken Snell (MVP) - 31 Jan 2008 03:00 GMT

> I must be missing something because from the break point, it seemed that
> the AfterUpdate code wasn't being used at all when the input mask was
> present. The code did not trip on that line and jumped right to the
> notinlist event.

The AfterUpdate event does not 'fire' until after the NotInList event is
completed. That is why you're not seeing the code breakpoint in the
AfterUpdate event procedure.

=

>> You haven't posted your code for the NotInList event, so I am just
>> guessing here as to what might be that code. But, if you use a separate
[quoted text clipped - 27 lines]
>
> End Sub

The NotInList code looks just fine to me, based on your observation that the
number being stored into the table does not have the input mask characters
in it. So, perhaps it is a timing issue. Try adding this code line right
after the Me.Requery step:
DoEvents

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message

Mike - 31 Jan 2008 20:39 GMT

Nope. Same thing.

I think we've given up. We're doing ok typing in 10 consecutive numbers.
Using the mask would be nice, but it doesn't seem meant to be :)

>> I must be missing something because from the break point, it seemed that
>> the AfterUpdate code wasn't being used at all when the input mask was
[quoted text clipped - 43 lines]
> line right after the Me.Requery step:
> DoEvents

Reply to this Message

Ken Snell (MVP) - 02 Feb 2008 04:43 GMT

> Nope. Same thing.
>
> I think we've given up. We're doing ok typing in 10 consecutive numbers.
> Using the mask would be nice, but it doesn't seem meant to be :)

Very weird that this behaves this way. I wish I had another suggestion to
give you.

Signature

Ken Snell
<MS ACCESS MVP>

Reply to this Message