Adding Value to an existing Combo box

[Buz]

Hi.
In table I created a combo box.
In the Form I would like the user to be able to choose from the drop down
menu the values already there but also to be able to add a new value as and
when but also to be able to have that value available with the existing
ones.
I know that when I type in a new value it only shows up on that record and
is not available again in a new record.
I saw in a sampe that you can set Access to pop up a message to the user to
dbl click the field, which then opens the table/form of which the combo box
belongs, so that they can enter the new value, close it and then choose it
from the refreshed combo box.
But I can't seem to get it right.
Please Help?

 

[fredg]

Hi.
In table I created a combo box.
In the Form I would like the user to be able to choose from the drop down
menu the values already there but also to be able to add a new value as and
when but also to be able to have that value available with the existing
ones.
I know that when I type in a new value it only shows up on that record and
is not available again in a new record.
I saw in a sampe that you can set Access to pop up a message to the user to
dbl click the field, which then opens the table/form of which the combo box
belongs, so that they can enter the new value, close it and then choose it
from the refreshed combo box.
But I can't seem to get it right.
Please Help?

Set the Combo Box LimitToList property to Yes.

Then code the Combo's NotInList event:

Private Sub ComboName_NotInList(NewData As String, Response As
Integer)

' Prompt user to verify they wish to add new value.
If MsgBox("Customer is not in the list. Add it?", vbOKCancel) = vbOK
Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
CurrentDb.Execute " INSERT INTO YourTableName(CustomerNameField)
SELECT '" & NewData & "';", dbFailOnError
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
' Clear the name from the combo box.
Me.ComboName = Null
End If

End Sub

Change YourTablename and CustomerNameField to whatever
the actual table and field names are.

Add your own error handling as needed.

 

[Buz]

Thank you . Just the thing!

Set the Combo Box LimitToList property to Yes.

Then code the Combo's NotInList event:

Private Sub ComboName_NotInList(NewData As String, Response As
Integer)

' Prompt user to verify they wish to add new value.
If MsgBox("Customer is not in the list. Add it?", vbOKCancel) = vbOK
Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
CurrentDb.Execute " INSERT INTO YourTableName(CustomerNameField)
SELECT '" & NewData & "';", dbFailOnError
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
' Clear the name from the combo box.
Me.ComboName = Null
End If

End Sub

Change YourTablename and CustomerNameField to whatever
the actual table and field names are.

Add your own error handling as needed.