Code not finding records even though they exist

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Argh, this worked perfectly in my test DB. I've migrated my old data into
the new db and now it doesn't work.

I have a combobox with a custom After Update event. Basically, you type in a
clients phone number, and it if exists it goes to that record. Otherwise, it
asks you to create a new one. All 5500 records have ten digit phone numbers
and there are no blanks. It's possible there are duplicates. 5500 is a lot
of records to verify duplicates in. Anyhow, this is the code in the After
Update event:

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

Problem is, not matter what I type for a number, it always passes to the
NotInList event. Even when the numbers are definitely already in the db.
Have I inadvertantly set a value in the combo box wrong or something? This
is the last thing to go to deploy the db. It really frustrates me because it
WAS working on the 10 test record i was messing around with.

Thanks
 
Ok, it IS finding them now but it's still hacking me off.

The field is a phone number field. I was using the standard phone number
input mask on the field. It would appear that now when i hit enter, the code
is looking up the phone number WITH the input mask on it. Even thought it
was not set to save the info with the mask. When not setting the field to
save the value with the input mask, i thought it was just there for looks
and the code should ignore it. Is that NOT the case? I suppose I could use a
mask of just ten 0's to make sure the limit the number to 10 characters, but
it would be beneficial to all if they saw the mask. Is that not doable or
have i missed something?
 
Mike

Use the query wizard for "find duplicates" ... that should make the
verification much easier.

How a value is stored and how it is display are two totally separate issues.
Is there a chance the the way the phone numbers are being displayed (i.e.,
formatted) doesn't match the way they are actually being stored? That might
be one reason why you are typing in what you consider to be a perfectly
acceptable phone number and being told "does not exist".

Have you tried using a parameterized query and doing the same search?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Well, i mentioned the input masking in a follow up that hadn't appear when
you responded, so there's that.

The numbers are stored like so: 1234567890

It would seem the 'value' of the combobox is including the parentheses and
dash. However, when the NotInList code is called it says "1234567890 is not
in the database. Would you like to add it?" If the formatting isn't getting
based to the second event, why is it seemingly there in the first?

Jeff Boyce said:
Mike

Use the query wizard for "find duplicates" ... that should make the
verification much easier.

How a value is stored and how it is display are two totally separate
issues. Is there a chance the the way the phone numbers are being
displayed (i.e., formatted) doesn't match the way they are actually being
stored? That might be one reason why you are typing in what you consider
to be a perfectly acceptable phone number and being told "does not exist".

Have you tried using a parameterized query and doing the same search?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mike said:
Argh, this worked perfectly in my test DB. I've migrated my old data into
the new db and now it doesn't work.

I have a combobox with a custom After Update event. Basically, you type
in a clients phone number, and it if exists it goes to that record.
Otherwise, it asks you to create a new one. All 5500 records have ten
digit phone numbers and there are no blanks. It's possible there are
duplicates. 5500 is a lot of records to verify duplicates in. Anyhow,
this is the code in the After Update event:

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

Problem is, not matter what I type for a number, it always passes to the
NotInList event. Even when the numbers are definitely already in the db.
Have I inadvertantly set a value in the combo box wrong or something?
This is the last thing to go to deploy the db. It really frustrates me
because it WAS working on the 10 test record i was messing around with.

Thanks
Click to expand...
Click to expand...
 
Back
Top