Combobox value and input masks

[cboath]

How do they relate? I was under the impression, that input masks had no bearing on anything unless you set it to save the information into your table with the masking information (i.e. the ()'s for area code and - (dash) for a phone number. It just doesn't seem to be working that way.

I have a combobox that looks up records based on the phone number you enter. If the number doesn't exist in the table, it asks you if you want to create a new record. Everything was fine - it worked just like i thought it should until I uploaded my old data (5000+ records) into the db and tried using it.

No matter what number i entered in the box for a phone number - it doesn't find it. The kicker, and reason it stopped finding the values was the input mask. When typing the values in with a mask of (000)000-000;;_ it doesn't look any values up (they don't seem to match) it just skips right to the NotInList event. When i delete the input mask, or use a mask of 0000000000;;_ it works just fine. The most frustrating part is that it worked perfectly using the input mask before I imported my old info. Could something in the import mess it up? It should be just raw data - nothing else.

Now where it gets interesting (to me at least) is the NotInList event looks up prompts you with a message box saying "the number 1234567890 is not in the db, do you want to create a new record?". To me, that implies that the properly unmasked information is getting into the code of it. Or am i missing something?

Again, all phone records are stored as 1234567890.
The input mask on the form is \(000\)000\-0000;;_
The code in the After Update that fails to find the record when the input mask exists is:

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

Why does it seem the AfterUpdate event is search for (123)456-7890 and the NotInList event is quoting 1234567890 in it's message box? Should it not be the same for both?

Thank you for reading,
Mike

P.S. - If the Notinlist code is needed it's below.

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.MyCombo = MeMyCombldValue
CurrentDb.Execute ("INSERT INTO Customer Table Table ([ID]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
.FindFirst "[Activity] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me.MyCombo.Undo
Response = acDataErrContinue
End If

 

[cboath]

No one has any clues?

 

[cboath]

Well, it appears the data in the phone field is just fine - no errors.

But it still remains that whatever is in the combobox when the input mask is used, never matches anything in the table - even when it should.

argg!

 

[wazz]

for what it's worth, i feel your pain. i've been trying to get a mask to work on a cbobox (date field) and it's not happening. my notinlist event seems to not work - it fires an error - but when i check the table, the new value is there (requery doesn't happen). i've tried the mask and/or formatting on the table, query and form and can't get around the problem. guessing that it has something to do with the fact that the (my) cbobox is based on a query to begin with...?

 

[wazz]

found this: http://support.microsoft.com/kb/824176/en-us. check out the 'more information' bit. ("...This problem might occur if the number that you type does not match the formatted entries of the underlying recordset that already exists.")

this helps me understand a bit more of what's happening with the dates i'm trying to enter. it's a bit weird though because i tried formatting the typed-in value before it gets added to the table and that didn't help. i can only get around this by trapping the error on the form_error event (err 2237).