combo box not accepting new data

[Guest]

"Bryan Black";"Chandra Sekhar";"Dharm";"Donald Smith";"Gregory
Sierchula";"Lisa L. Suk";"Robert Sytek";"Scott Harvey";"Thomas Nash";"Thomas
Winningham";"Trinity Burns"

I have created a combo box and have set the Row Source Type to a "Value
List". I have created the above list and typed this into the Row Source
field. I have created the following code for the "Not In List" property:

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

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo33
' 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


Everything works fine except that the new data does not get added to the Row
Source. I would also like it if I can sort the values once the new data gets
added. Can someone please help?

Rob

 

[Jeff L]

Not the best way to handle this. A value list works better if it is
static (doesn't change). I believe it would be better to make a table
to hold your values and use that as your Row Source. You can also sort
your values by doing that. I would suggest a table with NameId, FName,
LName as fields. Then your row source would be something like
Select NameId, FName & " " & LName From TheNewTable Order by LName

Set your bound column to 1, columns to 2, and column widths to 0;1
The field in your that you are using for storing these values would be
numeric, not text. The NameId will be stored, not the actual name.
You would use the NameID to look up the actual name.

Hope that helps!

 

[Guest]

Ok, I understand that a table would work better. Now, how do I change my
code to reflect to add the name to the table rather then at the end of the
value list.?

 

[Jeff L]

Create a data entry form that you can enter the first and last name
into. You will probably want a command button that will open this form
for you. When you close this form, requery your dropdown list.
Forms!MainFormName!DropDownListName.Requery

Substitute the names you used in the line above.