Data insertion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day,
I have a table with a field named "colors".
In the field are the colors blue, red, and green.
I have a form that has a combo box (unbound) to the "colors" field.
The users will choose a color already listed using this list.
How can I allow a user to add the color "yellow" to the list?
It seems only to allow what is already there.
I want the users to populate the list with more colors.
 
This code will prompt the user if they want to add new color.

Make sure that Limit to List is set to "Yes"
Insert this code on NotInList

Try this code

Private Sub cmbYourComboBox_NotInList(NewData As String, Response As Integer)
On Error GoTo handleErr
Dim db As DAO.Database, rs As DAO.Recordset

If MsgBox(" Would you like to add " & NewData & " to the list?", vbQuestion
+ vbOKCancel)= vbOK Then
DoCmd.RunCommand acCmdUndo
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
'On Error Resume Next
rs.AddNew
rs!colors= NewData
rs.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
rs.Close
Set rs = Nothing
Set db = Nothing
handleErr:
End Sub
 
Hello Armin,

Thank you for the response.
Limit to List is set to "Yes"
I inserted the code and changed the tables to my own.
The MsgBox pops up asking if the user wants to add, but the data is not
inserted upon clicking OK. Instead I am still getting the message to choose
from the list. I am using Access 2003 and wonder if "DAO" is what I should be
using?
Also I would ask another favor, can you point me in the direction of what to
read to understand this solution? If not I still thank you for your help.

Joseph
 
Back
Top