How to add a value not in the list to a bound combo box

[maupiti]

Access 2003

How to add a value not in the list to a bound combo box

Problem: The meesage box never comes up when I enter
a value that is not in the combo box.

Name: Product_Name
Limit To List: No
The combo box is bounded to a table that has only
one field. The name of this field is Product_Name.

I even set the Limit To List property to Yes, but the
message box still won 't comes up.

//////////////////////////////////////

Private Sub Product_Name_NotInList(NewData As String,
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Product_Name

' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", vbOKCancel)
= vbOK Then
' Set Response argument to indicate that
'data is being added.
Response = acDataErrAdded

' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel,
'suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

 

[PHV]

Set the LimittoList property to True.

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

 

[maupiti]

Hi PHV. Thank you for your help.
The Product_Name combo box is bound to a table,
and so I have to use an insert statement, but
Access complaint of a syntax error.


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

Dim ctl As Control
Dim Dbs As DAO.Database

Set Dbs = CurrentDb
Set ctl = Me!Product_Name

If MsgBox("Value is not in list. Add it?", vbOKCancel)
= vbOK Then
Dbs.Execute "INSERT INTO Table_Invoice_Product_Name (
Product_Name )" _
& " SELECT Table_Invoice_Product_Name.Product_Name, " _
& " FROM Table_Invoice_Product_Name " _
& " WHERE (((Table_Invoice_Product_Name.Product_Name)=[Forms]![Invoice_3]![Invoice_Detail_Subform_3]![Form]![Product_Name]));"

Else
'DO NOTHING
End If

End Sub

 

[PHV]

The syntax issue is simply the unnecessary comma at the end of the SELECT clause.
BUT, your insert query makes no sense for me ...

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

 

[CautionMP]

...
INSERT INTO Table_Invoice_Product_Name (Product_Name)
  VALUES (Forms]![Invoice_3]![Invoice_Detail_Subform_3]![Form]![Product_Name])...