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

 

[ProgramError]

As it says on the tin, you need to define what status is. Is it a control on the form?


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[Dom606]

Hi Ian,
Yes it is a control on a form.

 

[JBinQLD]

Try this tiny mod, it may help:

[red]me![/red]Status.RowSource = [red]me![/red]Status.RowSource & ";" & NewData

JB

 

[Dom606]

Hi JBinOLD,
Your suggestion worked. At least it compiles. When I go into the form and type in a value that is not on the list, first an error shows up saying the Status filed is not recognized. I click OK and then the message pops up say not in the list do you want to enter, etc. The code executes fine after that first error.

I suspect access does not recognize the Me!Status line of code.

' Return Control object that points to combo box.
Set ctl = Me!Status

 

[JBinQLD]

At least you're making progress mate!

Couple of ideas

1) rename the control - "status" sounds as though it should be a reserved word even if it possibly isn't. It could well be in future versions! Even just cmbStatus would be much better

2) Drop the ctl if it's causing drama. Just try replacing each instance with me!cmbStatus (Or me!status if you don't take advise above)

Good luck

JB

 

[MajP]

VB and VBA are very "sloppy" languages.

1)They allow you not to explicitly define variables
2)Provide different objects with the same name
3)Provide default properties and methods that do not have to be referenced

Being that most of us are lazy we like these features, but often this causes lots of problems.

In access the classic problem is declaring a recordset
dim rs as recordset

The problem is that there is 2 types of recordsets DAO and ADO and you get the one that is listed first in your references. So always define things explicitly when there are two types of objects with the same name.

dim rs as DAO.recordset

Same thin with controls and forms, there are two types: Access and MSForms.

dim ctl as Access.control
or much better
dim ctl as Access.combobox

If you know what the control is going to be never define it as a generic control! Only define it as a generic control if you are use different types of controls with the same code. In your case it is only a combobox

Here is the bigger kicker. Access allows you to give a field and a control the same name something like "Status". Personally I name my controls "cboStatus" and the field would be "status".

So when you go
me!status a
are you referring to the control or the field? Sometimes you do not know

So
dim cntl as access.combobox
set cntl = me.controls("cboStatus")
or in bang
set cntl = me.controls!cboStatus

(the above will return the control always and not the field with a like name)

or since you gave it a name different from the field the below will also always return the correct reference.
set cntl = me.cboStatus
set cntl = me!cboStatus
these would work .

Bottom line you have a field and a control with the same name (bad idea), and I believe that it looking for the field not the control.

 

[Dom606]

Thanks Jb and MajP,

My combobox name = cboLevel
My control source = Level
My row source = "xyz";"abc";"ect"
My Record source type = Value List

In the Not in List event I have this code:

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

Dim strSQL As String
Dim cntl As Access.ComboBox
  
' Return Control object that points to combo box.
Set cntl = Me.Controls("cboLevel")
' 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
  Me!Level.RowSource = Me!Level.RowSource & ";" & NewData
  cntl.Value = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
  Response = acDataErrContinue
  cntl.Undo
End If

Exit_cboLevel_NotInList:
Exit Sub

Err_cboLevel_NotInList:
MsgBox Err.Description
Resume Exit_cboLevel_NotInList

End Sub

Now the error message "Object does not support this property or method" pops up as soon as I enter a value not in the list. I select OK and the rest of the code runs and the value I added shows in value list.

 

[MajP]

What is "Level"?
Me!Level.RowSource

is that your cntl?

Comment out the On Error and show where it is breaking

 

[Dom606]

Level is the name of the field in the table.

I stepped through the code and it breaks here:

Me!Level.RowSource = Me!Level.RowSource & ";" & NewData

The value in Me!Level.Rowsource is the same as what is in NewData. The NewData captures the value that is not in the list. What is not working is for the code to read the actual RowSource for the cboLevel.

 

[MajP]

A field does not have a rowsource so of course it breaks.

A rowsource is a query or value list of the combo box or listbox. Look at the rowsource property in the help file for a better understanding.
again I think you want cntl.rowsource

 

[JBinQLD]

Here you go mate, for once I even tested it and it works fine here

You were close, just the name of the ccontrol wrong, and the & " ; " & Newdata... I've changed to include quottes like the other entries. And I changed your msgbox to yes/no!

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

Dim strSQL As String
Dim cntl As Access.ComboBox
  
' Return Control object that points to combo box.
Set cntl = Me.Controls("cboLevel")
' Prompt user to verify they wish to add new value.
If MsgBox("Item is not in list. Add it?", vbYesNo) = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to value list
  Me!cboLevel.RowSource = Me!cboLevel.RowSource & ";""" & NewData & """"
  cntl.Value = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
  Response = acDataErrContinue
  cntl.Undo
End If

Exit_cboLevel_NotInList:
Exit Sub

Err_cboLevel_NotInList:
MsgBox Err.Description
Resume Exit_cboLevel_NotInList
End Sub

Happy Dayz,

JB

 

[Dom606]

Jb,
Thanks for your persistence on this. I tried your suggested code and it does not error. It does add the newly added data to the table. However, it does not update the Value List with the added data. I stepped through the code and sure enough this line of code shows the added data:

[b]Me!cboLevel.RowSource[/b] = Me!cboLevel.RowSource & ";""" & NewData & """"

But the RowSource does not show the added data. I tried changing Me!cboLevel.RowSource to cntl.RowSource but that did not work either. I might have to ditch the entire thing and create a lookup table instead of trying to update the Value List.

 

[JBinQLD]

It certainly added it at my end. Try adding the line:

 me!cboLevel.requery

after the line you've shown above.

JB

 

[Dom606]

JBinQLD,
I did as you suggested and after the data is entered and I move to the field on the form and the error "you must save the record before doing a Requery"

I am using Access 2007, do you think that might be making a difference? After all, it worked for you. I am stumped.
Thanks
Dom

 

[JBinQLD]

Dom,

Guess it must be, I'm sticking with 2003 for a while but it's always nice to have a heads up about somethings that aren't going to work when I finally make the switch

Good luck, sorry I couldn't help any further

JB

 

[TheAceMan1]

How are ya Dom606 . . .

Be sure the [blue]Limit to List[/blue] property of the combo is set to [blue]Yes[/blue] and try the following:

[blue]   Dim CBx As ComboBox, DQ As String
     
   Set CBx = Me!cboLevel
   DQ = """"
   
   If MsgBox("Item is not in list. Add it?", vbYesNo) = vbYes Then
      CBx.RowSource = CBx.RowSource & ";" & DQ & NewData & DQ
      Response = acDataErrAdded
   Else
     Response = acDataErrContinue
     CBx.Undo
   End If
   
   Set CBx = Nothing[/blue]

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

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

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

 

[JBinQLD]

Aceman,

I tested the limit to list and the event doen't even trigger unless set so i assume that was already done.
I also thought the design view thing would be an issue but it worked perfect here so i figured that must be an exception. But -

Dom,

give it a go and good luck!

 

[TheAceMan1]

Howdy JBinQLD . . .

[blue]I tested the limit to list and the event doen't even trigger unless set so . . .[/blue]

When constructing a combo thru the wizard and selecting [blue]I will type in the values that I want[/blue], always sets:
RowSource Type [blue]Value List[/blue]
Limit To List [blue]No[/blue]

[blue]I also thought the design view thing would be an issue but it worked perfect here . . .[/blue]

During run time yes! What happens when you close then reopen the form? ... are the values still there?

See Ya! . . . . . .

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

 

[Dom606]

Hi guys,
I tried Acemans suggested code. And yes the Limit to List property of the combo is set to Yes.

The result is the same.

I stepped through the code and the RowSource works as it should. Meaning it takes the existing RowSource and adds the new value to the string. When I close and reopen the form, the RowSource only show the original list of values. For some reason the RowSource never updates to include the added value. No errors occur. Very frustrating.

The line of code "Response = acDataErrAdded" should (I think) come back and say Data Added. It does not. Could this be the problem?


Here is the code I have now:

Private Sub cboLevel_NotInList(NewData As String, Response As Integer)
'On Error GoTo Err_cboLevel_NotInList
Dim CBx As ComboBox, DQ As String
     
   Set CBx = Me!cboLevel
   DQ = """"
   
   If MsgBox("Item is not in list. Add it?", vbYesNo) = vbYes Then
      CBx.RowSource = CBx.RowSource & ";" & DQ & NewData & DQ
      Response = acDataErrAdded
   Else
     Response = acDataErrContinue
     CBx.Undo
   End If
   
   Set CBx = Nothing
   
Exit_cboLevel_NotInList:
Exit Sub

Err_cboLevel_NotInList:
MsgBox Err.Description
Resume Exit_cboLevel_NotInList
End Sub