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
A. 1234567890
or
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?
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 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
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 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?
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?
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.
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.
