Add to Combo box

[The Joker via AccessMonster.com]

Hello All,
I am working on a pretty basic "add to combo box" function. What I am
looking for is the user to have the option if they would like to add the new
information to the combo box. If they do it gets added (which works fine),
If they choose no I want them still to be able to submit the information in
the form but just not have it added to the combo box. How I currently have
it they have to add it or they can't use it, I get the "The text you entered
isn't in the list" error because I have set Limit to List "Yes". Technically
I don't want to limit to list. Any help will be greatly appreciated. Thanks.


Here is my code:

Private Sub Combo237_NotInList(NewData As String, Response As Integer)
' this is the combo box for prepayment penaltys "add to combo" feature

DoCmd.SetWarnings (False)

Dim strSql As String
If MsgBox(NewData & " is not in list, would you like to add it?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into tbl_GSPrepaypen (Prepayment) values('" & NewData &
"')"
CurrentDb.Execute strSql
Response = acDataErrAdded

End If
DoCmd.SetWarnings (True)


End Sub

 

[tina]

set the combo box's LimitToList property to No, and add the following code
to the combo box's AfterUpdate event, as

Private Sub Combo237_AfterUpdate()

If DCount("Prepayment", "tbl_GSPrepaypen", "Prepayment = '" _
& Me!Combo237 & "'") < 1 Then
If MsgBox("Add value to droplist?", _
vbYesNo + vbDefaultButton2) = vbYes Then
CurrentDb.Execute "INSERT INTO tbl_GSPrepaypen " _
& "( Prepayment ) SELECT '" & Me!Combo237 _
& "'", dbFailOnError
Me!Combo237.Requery
End If
End If

End Sub

hth

 

[Guest]

This site may help
http://www.pacificdb.com.au/MVP/Code/NIL.htm

 

[The Joker via AccessMonster.com]

Thanks for your quick and educated responses. It works perfectly.