Adding Value to an existing Combo box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Buz - 12 Apr 2005 17:46 GMT

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?

Reply to this Message

fredg - 12 Apr 2005 17:56 GMT

> Hi.
> In table I created a combo box.
[quoted text clipped - 10 lines]
> 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.

Signature

Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Reply to this Message

Buz - 12 Apr 2005 21:16 GMT

Thank you . Just the thing!

>> Hi.
>> In table I created a combo box.
[quoted text clipped - 43 lines]
>
> Add your own error handling as needed.

Reply to this Message