Updating Values in Combo Boxes

[Sunluva]

Forgive me if ive missed something simple here but how do i get a combo box to display values in a table or query and then add values to the table if the user enters new values?

For a simple example a combo box displays data from a table called "Names" which contains two fields, NameID (autonumber) and Name (text).

Ive tried changing the LimitToList property but it will only allow a "No" value if the width property of the first column (which is the bound column ie the NameID field) is set to non-zero value. However when the LimitToList is set to "No" the combo box only displays the NameID number and still wont allow any updates??.

Thanks in advance 4 ur comments.

 

[dalebeitz]

I know that a combo box will allow you to type values that do not exist in the pre-defined list from your NAMES table if you have the LimitToList property set to no, but I don't know of any way to have those new names written back to the table. A way may exist, but I've never done it that way.

What I've always done is create a separate form to manage my list table, which in this case is your NAMES. The user goes to this separate form to add, edit, or delete list items. Then you can use a combo box to allow the user to select from the list items. You can then set the LimitToList property to yes to prevent entry of values that aren't in your names table.

To make access to editing of names easier, you could put a command button on your entry form to open the Names form. I'm not sure if the combo box would be aware of the edits to the names table without opening and closing the form the combo box lives on. If it doesn't automatically update with the changes, I remember reading about a refresh command that could possibly be used to make the combo box aware of the changes. I've never tried this, but there might be a way to make it work.

Hope this helps!
Dale Beitz

 

[VicM]

Sunluva:

Add the following code to the AfterUpdate event of your combobox:

Me.comboboxname.Requery

That should update the combobox list after a new entry.

Hope this helps,

Vic

 

[Fahy]

As far as I remember this works

I've called the table Categories and combo box cboCategory.


Private Sub cboCategory_NotInList(NewData As String, Response As Integer)
Dim strMsg As String, rst As Recordset, db As Database

strMsg = "'" & NewData & "' is not in the category list. "
strMsg = strMsg & "Would you like to add it?"
If MsgBox(strMsg, vbYesNo, "New Category&quot = vbNo Then
Me.Undo
Exit Sub
Else
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Categories&quot
rst.AddNew
rst!Category = NewData
rst.Update
rst.Close
End If

End Sub