Add item to a Row Source value list and re-sort through VBA

[Dor100]

Does anyone know of a good clean sub to provide users with a way to add to and re-sort the value list behind a combo box row source on the fly (where Limit To List is set to yes)? I know I could simply use a separate table with sql as an alternative, but that option may not be desirable now. This would be a nice feature. Thanks.

 

[kjv1611]

Try searching around for code for the _NotInList event, and you should find what you need. I use some code myself so that if a user types something that is "not in the list", then the user is asked if he/she wants to add it. If the user selects 'yes' or 'ok', then the value is added to the recordset, thus the table, and if not, then it is not added, and the user is left at that same combobox to re-enter the value. For the sorting part, you could have a couple of buttons next to the text box which would requery the combobox, possibly re-sorting the list there - if that's possible...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Zion7]

Could you not simply toggle between the Limit To List property?

Private Sub ComboBox_OnFocus()
ComboBox.LimitToList = False

Private Sub ComboBox_AfterUpdate()
Me.Refresh
ComboBox.LimitToList = True

Hope this helps, Good Luck!

 

[Dor100]

Thanks to both of you for your input. The elusive goal I would really love to get hold of is to give people a button by which they could actually 'unlock' the semi-colon-delimited Value List which appears in the field properties window as the row source, add an item to it, and then have it re-sorted in proper order for the next time someone clicks the dropdown arrow for a given combo box, as if they had gone into design view and done it there themselves.

What I sometimes do as a substitute is simply store all available input choices in a separate table, which generally only requires one field, and query it as the row source. Instead of having a regular label next to the combo box, I make it a button that takes you to the table where you can add options at will. This works, but I think there must be a way to do the other.

 

[Zion7]

Private Sub cmdUpdateComboBox_Click()
With Me.ComboBox
.Locked = False
.LimitToList = False

Dim sRowSource As String
sRowSource = .RowSource

.RowSource = sRowSource & "; " & .Value

.Locked = True
.LimitToList = True

End With
End Sub

Something like this, give or take?

Good Luck!

 

[Dor100]

Thanks, dboulos - that's a nice clean routine. It looks like that may be as far as one can go with this plan, unless there's a way to force Access to 'read' the newly appended-to value list, sort it according to a particular order, and preserve semi-colons. (My gut feeling is to doubt that now based on experience, although I normally believe in possibilities.) I may need to stick with my current method or take a closer look at Stephen's material. Thanks again to both of you.