Not In List Problems

[NotGood@All]

I have a Table named “SentToWhoâ€, with 2 fields, “SentToId†, AutoNumber &
SentTo, text, I have a form with a Combo box named “SendToId†& Control
Source is “SentToIdâ€, the Row Source is a query that uses the table
“SentToWhoâ€, with both fields. In the “On Not in List†I have the following
code but the combo box won’t let me tab away from it or enter the new record.

Private Sub SentToId_NotInList(NewData As String, Response As Integer)
Dim s As String
Dim mRecordID As Long
Dim mText As String

If Len(Trim(NewData)) = 0 Then Exit Sub

mText = StrConv(NewData, vbProperCase)
s = "INSERT INTO SentToWho (SentTo) " _
& " SELECT '" & mText & "';"

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("SentToID", "SentToWho"))

If mRecordID > 0 Then
Response = acDataErrAdded

Me.SentToId = mRecordID
Else
Response = acDataErrContinue
End If
End Sub

 

[Steve Sanford]

Using your info, I created a table, a form, a text box and a combo box. I
added the not in list code (no changes).

I didn't get an error. Code ran and names were added to the table and
displayed in the combo box.

Do you have the column count of the combo box = 2? (format tab)
And Column widths set to 0;1.5
Do you have "Option Explicit" (no quotes) at the top of the module?
Does the module compile?

Put a breakpoint at the first line of the not in list code. Then try and add
a name.
What error message do you get??

HTH

 

[NotGood@All]

Steve, thanks, I didn't have the column count. Works good now.

Thanks