**Add Item to Value list in Combo Box**

[John M]

I have a combo box with the following properties:

Row Source Type: Value List
Row Source: Item A;Item B;Item C

Can I program a button on the form so that the user can add "item D" without switching to design mode.

Thanks.

 

[boblarson]

You'd be much better off if you change it to run off of a table, or a query off the table. Then you can set it up to allow changes a WHOLE lot easier than doing it with what you're doing now.

You CAN change the rowsource with code at runtime, but the changes will NOT persist and when you close the form that the combo box is on your changes will be lost. Unless you set a public variable, and then it will be lost when the database closes.

So, if you want the changes to be available for any time after they are made, then setting up a table for the values will be your best bet.

BL
hth

 

[John M]

Thank you!!

 

[llkhoutx]

The following is an example of adding to a table via the combobox "NotInList" Event:

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)
Dim intCurrentID As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

If vbNo = MsgBox("Add a new Client to the Names table?", vbQuestion + vbYesNo, "User action required") Then
Response = acDataErrContinue
Exit Sub
End If

intCurrentID = GetNextNameID()
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbNames", dbOpenDynaset)
rs.AddNew
rs.Fields(0) = intCurrentID
rs.Fields(1) = NewData
rs.Update
rs.Close
Set rs = Nothing
Response = acDataErrAdded

Set rs = db.OpenRecordset("JCNNameFiles", dbOpenDynaset)
rs.AddNew
rs!FileID = Me!cboFileID
rs!NameID = intCurrentID
rs.Update
Set db = Nothing

Forms!hfrmScratch!sName = NewData
If vbNo = MsgBox("Add detail to the Name record just created?", vbQuestion + vbYesNo, "User action required") Then
Me!lstClients.Requery
Exit Sub
End If
Me!lstClients.Requery
DoCmd.OpenForm "frmNameDetail"
Forms!frmNameDetail.Requery

End Sub


GetNextNameID is a simple function which gets the next unique index id for the table. I don't use the autonumber data type.

 

[John M]

Thanks llkhoutx. I think your solution is very elegant. Thanks a bunch.

 

[thart21]

Not In List Event - update multiple fields

I have a form with 5 fields, the first being a combo box, the rest text. I can get the code to work to add a new record via the NotInList event only in the combo box, but need to enter all of the information for the new record i.e. DocNumber, Revision, Description, DateCompleted and Hours. How can I modify this code to update more than one field?

Thanks!

Toni