Cannot add new value to combo box 1

[annie52]

LimitToList is set to True in my cbo box. When I attempt to add a value that is not in the list, my code runs and is followed by:

1. "Error #0" message. This error has no description.
2. "Characters found after end of SQL statement" message.
3. "Error #0" message again.
4. "The text you entered isn't an item in the list" message.

I'm totally lost and hope somebody sees what I'm doing wrong. Here's the code I'm using:
============================================
On Error GoTo Err_POC_NotInList

Dim ctl As Control
Set ctl = Forms!frmReqnWatch!POC

If MsgBox("That POC is not listed in the database." & Chr(10) & Chr(13) _
& " Do you want to add this name?", vbYesNo, "RPP Tracking System") = vbYes Then
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
Response = acDataErrContinue
ctl.Undo
End If

Exit_POC_NotInList:
Exit Sub

Err_POC_NotInList:
MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) & Err.Description, vbOKOnly, _
"RPP Tracking System"
Resume Exit_POC_NotInList

End Sub

 

[dhookom]

What is the Row Source and Row Source Type of your combo box? I expect it is a table or query. If so, you will need to append a record to the table.

If you could stick a value to the end of the Row Source property, it would be a temporary fix and would evaporate when the form was closed.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Figured out your problem. The code you're using is intended to add an item if the combobox Row Source Type is set to Value List, i.e. if you typed in the values you wanted when creating the control. But your combobx Row Source is actually set to Table/Query. The line

ctl.RowSource = ctl.RowSource & ";" & NewData

is appending

";" & NewData

to the end of the SQL statement Access generated for the Row Source for the table/query.

TO add items to a combobx based on a table/query you need to open a form and actually add the item to the underlying table.


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Got distracted in the middle of posting and didn't see Duane's response. It's right on the money; I recreated the OPs scenario and got the same errors by doing exactly what Duane stated. IF the cbo is based on a Value List the code works as expected.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[annie52]

Hi, Duane. The combo box is based on a table that holds only one field. I really don't want to have to open a new form that appends the new entry to the table. I want it to append "automatically" when the user clicks on the Yes button.

"If you could stick a value to the end of the Row Source property..." Sorry, but I don't understand this. Would you please explain in more detail.

Thanks!
Ann

 

[dhookom]

What I meant by "stick" is to append a record to a table. Try code like:

Private Sub cboColor_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim strMsg as String
    strMsg ="Do you want to add the color to the table?"
    If MsgBox(strMsg, vbYesNo + vbQuestion, "Add Color") = vbYes Then
        strSQL = "INSERT INTO tblColors (Color) VALUES ('" & NewData & "')"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
     Else
        Me.cboColor.Undo
        Response = acDataErrContinue
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

Okay, thanks. Now, my procedure runs all the way through, adds the new POC to the table (also called POC), and correctly exits. However, after leaving the sub, I still get the same four messages that I reported originally.

 

[annie52]

I was causing confusion in my code because I forgot to delete the line:
ctl.RowSource = ctl.RowSource & ";" & NewData

All is good now. Thanks for your great help!