NotInList problem -- get custom entry box AND generic message -- Access 2002

[Clark via AccessMonster.com]

This problem has plagued me so long I've reduced it to one combo box on one
form and one table to eliminate extraneous input. The table has two fields,
WordID and Word. The combo box allows the user to enter a word. If it is
not in the table, a message box asks if he/she wants to add it. If yes,
messagebox disappears and the generic messagebox opens with "The item you
entered isn't an item in the list." The entered word remains visible in the
combo box, though it has been entered into the table.
Limit to List = yes. Auto Expand = yes.
The code is:
Private Sub Word_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Word ...")
If i = vbYes Then
strSQL = "Insert Into tblWord(Word) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Any ideas?

 

[Dale Fye]

Clark,

I had similiar problem a while back. It took me forever to figure out what
was happening, and how to get around it. It appeared that I was not able to
insert the value into the table while the table was bound to the control. I
cannot find my code right now, but in a short explaination, what I did was
to set the controls rowsource to "", then do the insert, get the value of
the key in the table, then reset the controls rowsource, then set the
controls value to the new value.

It was something like:

Private Sub Word_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String
Dim db as dao.database
Dim rs as dao.recordset
Dim NewID as long

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Word ...")
If i = vbYes Then
Set db = currentdb
strSQL = me.word.rowsource
me.word.rowsource = ""
Set rs = db.openrecordset(strSQL)
With rs
rs.addnew
rs("Word") = NewData
rs.update
NewId = rs("WordID")
end with
me.word.rowsource = strsql
me.word = NewID
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

HTH
Dale

 

[SteveS]

This problem has plagued me so long I've reduced it to one combo box on one
form and one table to eliminate extraneous input. The table has two fields,
WordID and Word. The combo box allows the user to enter a word. If it is
not in the table, a message box asks if he/she wants to add it. If yes,
messagebox disappears and the generic messagebox opens with "The item you
entered isn't an item in the list." The entered word remains visible in the
combo box, though it has been entered into the table.
Limit to List = yes. Auto Expand = yes.
The code is:
Private Sub Word_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Word ...")
If i = vbYes Then
strSQL = "Insert Into tblWord(Word) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


Any ideas?

When I had problems with the NotInList event, I found three places that
really helped me understand what I was doing wrong:

1) http://www.mvps.org/access/forms/frm0015.htm


http://www.rogersaccesslibrary.com/TableOfContents3.asp#N

which has these two pages:

2)
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInList.mdb

3)
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInListAlternatives.mdb

 

[Clark via AccessMonster.com]

Revised code (below) fixes the problem. BUT -- the NotInList function
doesn't work. Any word I type in the control yields a message box saying
the word is not in the list and asking if I'd like to add it. Help!

Private Sub cboWord_NotInList(NewData As String, Response As Integer)
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

'Custom message box
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Word . . .")
If i = vbYes Then
CurrentDb.Execute "INSERT INTO tblWord(Word) VALUES ('" & NewData &
"');", dbFailOnError

Response = acDataErrContinue

Else

Me.cboWord.Undo

Response = acDataErrContinue

End If

Me.cboWord.Undo

End Sub