Not In List - RowSource Conflict

[scott]

I have a combo with a NotInList event that opens a pop-up for the new
record, etc. My only flaw is being caused when the user types in
"LastName,FirstName" instead of "LastName, FirstName" with a space after the
comma. My pop-up form is working correctly and accepting the first/last name
pair and even saves the new name even when the "space" is omitted.

The problem is my combo "cbo_userID" produces a "this item isn't in list"
error only when the "LastName, FirstName" name pair is entered into the
combo with no space between the comma and first name. For example,
"Jones,Mike" will display the error, while "Jones, Mike" is fine. The twist
is that "Jones,Mike" actually exists in the combo after the "NotInList"
routine finishes, but since my combo control's RowSource sql concatenation
uses the "comma space", the combo doesn't know that "Jones,Mike" has been
entered and even appears in the combo's selection list.

I tried forcing the new name entry into the combo through the BeforeUpdate
event (see cbo_userID_BeforeUpdate below), but I get the same "this item
isn't in list" error. I listed my combo's Rowsource SQL below, as well as
the other working code. The only non-working code below is the
cbo_userID_BeforeUpdate sub.

I'm almost positive that no "error handling" code will fix this problem. Any
ideas?



Combo RowSource SQL: ***************
SELECT userID, COALESCE (NULLIF (userLast, ''), '') + COALESCE (', ' +
NULLIF (userFirst, ''), '') AS userName FROM t_users u ORDER BY userLast,
userFirst



CODE: ******************************

Private Sub cbo_userID_BeforeUpdate(Cancel As Integer)
' My attempt to force the new entry using a space
Dim sName As String, sLast As String, sFirst As String, iComma As
Integer

sName = Me.cbo_userID.Column(1)
iComma = InStr(sName, ",")

If iComma = 0 Then
'sLast = sName
Else
sLast = Left(sName, iComma - 1)
sFirst = Trim(Mid(sName, intComma + 1))

Me.cbo_userID.Column(1) = sLast & ", " & sFirst
End If


End Sub


Private Sub cbo_userID_AfterUpdate()
On Error GoTo HandleErr

Me.Requery
DoCmd.RunCommand acCmdSaveRecord
Me.Refresh

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Dim Response As Integer
Case 515 ' this error is caused when no user logon name is present
On Error Resume Next
Response = acDataErrAdded

'MsgBox Err & " Caught 515: " & Err.Description
Err.Clear
Response = acDataErrContinue
Case Else
On Error Resume Next
Response = acDataErrAdded

'MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in f_email_sub_add.cbo_userID_AfterUpdate Else"
Err.Clear
Response = acDataErrContinue
End Select
Resume ExitHere
Resume

End Sub

Private Sub cbo_userID_NotInList(NewData As String, Response As Integer)
On Error GoTo HandleErr

Dim sName As String, sLast As String, sFirst As String, iComma As
Integer
Dim iReturn As Integer, varName As Variant

sName = NewData
iComma = InStr(sName, ",")

If iComma = 0 Then
sLast = sName
Else
sLast = Left(sName, iComma - 1)
sFirst = Trim(Mid(sName, intComma + 1))
End If

iReturn = MsgBox("The user " & sName & _
" is not in the system. Do you want to add this User?", _
vbQuestion + vbYesNo, CurrentProject.Properties("AppTitle").Value)
If iReturn = vbYes Then
DoCmd.OpenForm FormName:="frmUserAdd", _
DataMode:=acAdd, WindowMode:=acDialog, OpenArgs:=sName
Response = acDataErrAdded
ElseIf iReturn = vbNo Then
Response = acDataErrContinue
End If

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case 515 ' this error is caused when no user logon name is present
On Error Resume Next
Response = acDataErrAdded

'MsgBox Err & " Caught 2: " & Err.Description
Err.Clear
Response = acDataErrContinue
Case Else
On Error Resume Next
Response = acDataErrAdded

'MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in frmUserAdd.cbo_userID_NotInList Else"
Err.Clear
Response = acDataErrContinue
End Select
Resume ExitHere
Resume

End Sub