Solved Possible to add additonal items in a combobox? (1 Viewer)

[hhag]

Hi,
I've a combobox, linked/bounded to a table, where I choose a town. I would like to have the possibility to, when needed, add a new town, that doesn't exist in the table and then next time this new town should be present in the table of choice. This must be possible to do with some nive VBA code. Maybe it isn't possible to write the new town in the combobox directly? Maybe a command button should be inluded in order to start an inputbox, get the information inside the table (may the combobox should be sourced through a query so you can sort in an aplhabetic way), and then the new town is selectable via a requery?

Does anyone have any idea?

 

[Minty]

A combo box has the not in list property which allows you to perform another action such as open a form to add the new item.

This thread may help https://www.access-programmers.co.uk/forums/threads/change-combo-box-not-in-list-with-vba.180983/

 

[arnelgp]

edit your form and select the combobox.
on it's Property Sheet->Data, set Limit To List to Yes.
on Event, Not In List, choose Code Builder:

Private Sub yourCombo_NotInList(NewData As String, Response As Integer)
    
    Response = acDataErrContinue
    
    If MsgBox(NewData & " is not in table, add it ?", vbYesNo, "New Data") = vbYes Then
        
        CurrentDb.Execute "insert into [yourtableName] ([FieldName]) select '" & NewData & "'"
        
        Response = acDataErrAdded
        
    End If
End Sub

replace yourTableName with the bound table of combo.
replace FieldName with the correct field name of that table.

 

[Galaxiom]

Be careful about allowing all users to add names to the list. They are notorious for misspelling words then going ahead and blindly adding them to the list if that option is available. It is a good idea to record who adds the new entries so you have a better idea who to trust as you accumulate new entries.

One strategy is to filter the list as they type in the hope of hitting a match after a few characters which may also save data entry effort and time. Of course that doesn't work if they make a mistake early in the name. Comparing what has been typed with the existing list using a Damearu-Levensthein distance function can bring up an ordered list of typographically similar names.

Have a look at the function in Post 10 of this thread.