"Not In List" Code Problem - Yes/No Button 1

[NanG1]

I have a Combo Box and have added an Event (VBA Code) so that users may add a value to the Combo Box List. I 'did' set the "Limit to List" to YES.
The Event "does run" and users "can add" a new value to the list. But if the user wants to simply add a value to the top form without adding it to the subform (combo box list), they can't do that. I thought that by answering NO on the button the user would be able to keep the typed value in the text box (without adding it to the list) and then tab to the next control. But the box keeps popping up and allowing only 1)something to be added to the list,or 2)the text box entry to be deleted so that the user may move on and tab to the next control.
How can I make the NO button work so that the user can add a value to the text box (top form) without adding it to the list?
Here is my code. Any guidance would be greatly appreciated, as I have spent about 5 hours today just trying to figure this out.

Private Sub Customer_Name_NotInList(NewData As String, Response As Integer)
On Error GoTo Customer_Name_NotInList_Err
' Add a new record to the Customer Name table
' and requery the Customer Name combo box
Dim NewName As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
'Make sure the user really wants to add it
MsgTitle = "Customer Name is not on the list"
MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
NewName = MsgBox("Do you want to add the new Customer Name?", MsgDialog, MsgTitle)
If NewName = IDNO Then
Response = acDataErrContinue
Else
DoCmd.OpenForm "Customer Name Input Form", acNormal, , , acAdd, acDialog
Response = acDataErrAdded
End If
Customer_Name_Exit:
Exit Sub
Customer_Name_NotInList_Err:
MsgBox Err.Description
Resume Customer_Name_Exit
End Sub

 

[dRahme]

Hi, You might consider taking a slightly different approach and use the AfterUpdate Event of your main text box.

Below is some code where the user inputs a stock symbol. Dlookup is used to search for the ticker. If it is not found in the stock table, then it asks for an add. If it is in the stock table, it is ignored.

Private Sub txtNewStock_AfterUpdate()

Dim Response, NewStock, AddStock As String

NewStock = Me.txtNewStock

If IsNull(DLookup("[ticker]", "tblStocks", "[ticker]= '" & NewStock & "'")) Then

AddStock = 1

End If

If AddStock = "1" Then

Response = MsgBox("Do You Wish to Add this Stock?", vbYesNo, "AddStock?")

End If

Select Case Response

Case Is = vbYes

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblStocks ( Ticker )" & _
"SELECT [Forms]![Form1]![txtNewStock] AS NewStock; "

DoCmd.SetWarnings True

MsgBox "Ok, Stock Added"

Case Is = vbNo

MsgBox "Ok, Not Adding Stock"

End Select

End Sub

 

[NanG1]

Thank you for the alternative idea. I am new to Access and the only routine I have read about to add a value to a lookup value in a subform (from a main form) was the Not In List event.
One other question... My table has 2 words in the name, do I put an underscore (_) between the words? I'll try it in the meantime, TIA for your help.
NanG

 

[PHV]

My table has 2 words in the name
Use brackets: [Table name]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NanG1]

Hmmm, I tried this (although I may have some of the words wrong). But right off the bat, I still get a message saying that the text I entered is not on the list, and either select an item from the list or type an item from the list.
I am not sure (as I mentioned before) if I should use an underscore anywhere. Also, I don't know what the FOrms! code means.
Thanks for any further ideas.

 

[NanG1]

If I use brackets to enclose the table name, do I also use the abbrv. tbl in front of the brackets?

 

[NanG1]

Here is the new code I have entered, based on the previous replies to my post, where my table name is OEM Name and the Control name is OEM Name. O am also not sure if I have the indentaiton correct on the various commands.

Private Sub txtOEM_Name_AfterUpdate()
Dim Response, NewOEMName, AddName As String
NewOEMName = Me.txtNewName
If IsNull(DLookup("[OEM_Name]", "tbl[OEM Name]", "[OEM_Name]= '" & NewName & "'")) Then
AddName = 1
End If
If AddName = "1" Then
Response = MsgBox("Do You Wish to Add this OEM Name?", vbYesNo, "AddName?")
End If
Select Case Response
Case Is = vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl[OEM Name] ( OEM_Name )" & _
"SELECT [Forms]![Form1]![txtNewName] AS NewName; "
DoCmd.SetWarnings True
MsgBox "Ok, Name Added"
Case Is = vbNo
MsgBox "Ok, Not Adding Name"
End Select
End Sub

 

[dRahme]

Hi, it looks ok to me. Does it work?