Not in list

[Grapmag]

I have a lookup combobox to select a phone number, with input mask including the "-"(123-1234 for example). The literal is saved with the number in the table. The combo is limited to the list. The not-in-list event brings up a form to add a new number (with some other data required). The problem lies in that the acdataerradded does not suppress the error message. My theory is that since the new entry in the lookup combo does not include the literal, the not in list event fires again (because the add new entry form saved the new entry with the literal). Do I have this correct? How do I get the proper behavior?

Private Sub PharmID_NotInList(NewData As String, Response As Integer)
Dim intAns As Integer
'Open add pharmacy form in dialog format, open args = PharmID.text (NewData)
intAns = MsgBox("That number is not in the database. Do you want to add it?", vbYesNo, "New pharmacy?&quot
If intAns = vbYes Then
NewData = Format(NewData, "&&&-&&&&&quot
DoCmd.OpenForm "frmPharmacy", , , , acAdd, acDialog, NewData
If IsNull(DLookup("Name", "tblPharmacy", "[ph1] = """ & NewData & """&quot) Then
MsgBox "It's null!"
Response = acDataErrContinue
Else
MsgBox "That pharmacy number is now in the list"
Response = acDataErrAdded
End If
Exit Sub
End If
Response = acDataErrDisplay

 

[BillPower]

Hi Grapmag,

Try adding Exit Sub, it seems to me that whatever the user enters, they're going to get a message.

Private Sub PharmID_NotInList(NewData As String, Response As Integer)
Dim intAns As Integer
'Open add pharmacy form in dialog format, open args = PharmID.text (NewData)
intAns = MsgBox("That number is not in the database. Do you want to add it?", vbYesNo, "New pharmacy?&quot
If intAns = vbYes Then
NewData = Format(NewData, "&&&-&&&&&quot
DoCmd.OpenForm "frmPharmacy", , , , acAdd, acDialog, NewData
If IsNull(DLookup("Name", "tblPharmacy", "[ph1] = """ & NewData & """&quot) Then
MsgBox "It's null!"
Response = acDataErrContinue
exit sub
Else
MsgBox "That pharmacy number is now in the list"
Response = acDataErrAdded
exit sub
End If
Exit Sub
End If
Response = acDataErrDisplay

 

[Grapmag]

Didn't help, but thanks. The problem lies in that the input mask is not recognized by the "not in list" event. If a user enters a new number in the combo box with the mask on the number appears as 123-1234 ("-" is the literal in the input mask !000\-0000;0;_. However, when "not in list" fires NewData has the value 1231234 (missing the literal). Format(NewData, "&&&-&&&&&quot passes the correct form of the number to the my "add new number" form, but then the number is saved with the literal in place and "not in list" fires again (because the combo value does not have the literal; the list has 123-1234 and the combo value entered by the user is still 1231234. I want the combo list to show the form with "-" because it is easier to read and dial. How do I get around this?