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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200603/1

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


"The Joker via AccessMonster.com" <u17775@uwe> wrote in message
news:5e16c398da84e@uwe...
> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200603/1

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

"The Joker via AccessMonster.com" wrote:

> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200603/1
>

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

--
Message posted via http://www.accessmonster.com