NotInList will not allow me to modify NewData

[tlyczko]

I have a NotInList event which prompts the user if they want to add the
data to the combo box, exits if they don't, with Response =
acDataErrContinue and starts over.

If the user wants to add the data, I check with:

If Len(NewData) = 0 Or IsNull(NewData) Or InStr(NewData, ",") = False
Or CountCommas(NewData) > 1 Or Right$(NewData, 1) = "," Then
Call MsgBox("Please enter a name in the Response field," & vbCrLf &
"in the format Last Name, First Name.", vbExclamation Or vbSystemModal,
"Required Data Entry")
Response = acDataErrContinue '5/13/05 don't proceed further
GoTo Exit_Rspns_NotInList
End If

This works fine.

Then if it has passed the above tests, I want to:

'insert a space after the comma if there is none
If InStr(NewData, ",") > 1 And InStr(NewData, ", ") = 0 Then
NewData = Replace(NewData, ",", ", ")
End If

'set data to proper case
NewData = StrConv(NewData, vbProperCase)

before adding the data to the bound combo box's data table after which
I invoke Response = acDataErrAdded and things should work properly.

If I type the data with the correct format (a space after the comma)
the StrConv works properly and all is well.

If I don't use the proper format (no space after the comma) I get the
standard NotInList error, the NewData is properly changed, the
corrected name is added to the list but it is not shown in the combo
box field where it should be shown if I used the proper format.

I have tried using acDataErrContinue but I still get the NotInList
error message.

Moving Response = acDataErrAdded around doesn't seem to help, either.

Any suggestions???

Thank you,
Tom

Private Sub Rspns_NotInList(NewData As String, Response As Integer)
'5/11/05 custom code to determine whether to add it to the list
If Me.QstnID = 82 Then
If MsgBox("The value you just entered (" & NewData & ") " & _
"is not in the list. Do you want to add it? " _
, vbQuestion + vbYesNo, "Add to list?") = vbNo Then
' They don't want to add it ... goto to exit label
Response = acDataErrContinue
GoTo Exit_Rspns_NotInList
Else 'ADD it to the dropdown list
'5/13/05 ensure proper data entry format, code is from
frmSurveyResponses
If Len(NewData) = 0 Or IsNull(NewData) Or InStr(NewData, ",") = False
Or CountCommas(NewData) > 1 Or Right$(NewData, 1) = "," Then
Call MsgBox("Please enter a name in the Response field," & vbCrLf &
"in the format Last Name, First Name.", vbExclamation Or vbSystemModal,
"Required Data Entry")
Response = acDataErrContinue '5/13/05 don't proceed further
GoTo Exit_Rspns_NotInList
End If
'5/13/05 fix commas
If InStr(NewData, ", ") = 0 Then
NewData = Replace(NewData, ",", ", ")
End If
'5/13/05 set name to proper case
NewData = StrConv(NewData, vbProperCase)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblResponsesList")
On Error Resume Next
rs.AddNew
rs.Fields("QstnID") = CLng(82)
rs.Fields("Rspns") = CStr(NewData)
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
Response = acDataErrAdded
End If
End If

Exit_Rspns_NotInList:
Me.Undo
Exit Sub

Err_Rspns_NotInList:
GoTo Exit_Rspns_NotInList
End Sub

 

[Graham R Seach]

This may help you to understand how to use the NotInList event:
http://www.pacificdb.com.au/MVP/Code/NIL.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------