Allow a user to add values to the underlying Value List

[Dom606]

Combobox - Allow a user to add values to the underlying Value List
faq181-110

I used the code in the faq181-110 and when I try to compile the code it errors on the Status.RowSource as not being defined.

I am using Access 2007, has something changed where this bit of code will no longer work?

Private Sub cboLevel_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboLevel_NotInList

Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me!Status
' Prompt user to verify they wish to add new value.
If MsgBox("Item 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 value list
  Status.RowSource = [COLOR=red]Status.RowSource[/color] & ";" & NewData
ctl.Value = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_cboLevel_NotInList:
Exit Sub

Err_cboLevel_NotInListt:
MsgBox Err.Description
Resume Exit_cboLevel_NotInList

End Sub

 

[JBinQLD]

My hands are up, i was suprised too but i didn't try it after re-opening the form!

To get around this build the rowsource on open of the form. The code above allows successful addition to the table so on open simply iterate through the list of distinct table entries and populate with those.

There are better ways of course, but just out of interest that should work pretty wicked

JB

 

[TheAceMan1]

Dom606 . . .

I repeat:

[blue]Also be aware: changes are not permanent unless the form is in design view! [purple]It may be better to construct a table for this[/purple], which would change the code.[/blue]

[purple]The table gives you permanency![/purple]

In the [blue]Not in List[/blue] event you'll have to use an [blue]insert SQL[/blue] instead . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Dom606]

Thanks guys. I am going to create a lookup table of values and use the good old Not In List code that has worked before.

Dom

 

[Dom606]

All,
I received a lot of help from the folks here regarding this question. Ultimately, I ended up creating a look up table for values as opposed to trying to update a value list.

I found this article posted on Allen Browne's tips for Microsoft Access page and thought I would pass it on. It describes why you should not use value list.

The article is located at this URL

http://allenbrowne.com/ser-27.html

Scroll down to this explanation.
Option 4: Add items to a Value List

I am using Access 2007, and the changes in this version have caused me more trouble than I care to mention.

Thanks again for all your help.
Dom