Combo item not in list

[fileman1]

I have a combo control where I want to let the user add selections.

The combo is bound to a table, and has the property set to limit to list.
I get a notice after putting in a word that I have put something not in the list, and when I go to put the form into design view, again it tells me it's not in the list, and then tells me it cannot be saved at this time. However it has gone into the combo list.
Any ideas please. The combo is on an unbound main form form .

Private Sub Combo867_NotInList(NewData As String, Response As Integer)

    Dim db As DAO.Database, rst As DAO.Recordset, SQL As String

    Response = False
    If MsgBox("The item is not in list. Add it?", vbYesNo) = vbYes Then
        Set db = CurrentDb()

        SQL = "SELECT * FROM Category;"

        Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
        rst.AddNew
        rst![Category] = NewData
        rst.Update
        Set rst = Nothing
        Response = acDataErrAdded
        DoEvents

    Else
        Response = acDataErrContinue

    End If

End Sub

 

[softhemc]

I haven't used Access for a long time, however although you have added the new row to the table, you don't seem to have refreshed the combobox source to get it to reload from the underlying table. I can't remember, maybe Access does that automatically, but in VB.NET using disconnected recordsets I would manually have refresh the combobox.

 

[fileman1]

Thanks for the idea, however I tried everywhere to requery the combo but did not cure. I tried first requery after the update statement etc, but got error message 2118, You must save the current file before you run requery. I thought after the line rst.update it was saved. I tried at the end of the event, but still get the chain of notices popping up.
1st The item is not in the list, do you want to add? Y/N
2nd The item is not in the list, please select an item from the list.
3rd, when going into form design I get a repeat notice the item is not in the list, and then unable to save record at this time.

 

[PHV]

I'd try this:
...
Response = acDataErrAdded
Me!Combo867.RowSource = Me!Combo867.RowSource
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

+
Thanks PHV, sorry late reply, only just came back and saw. Unfortunately still does not work.
I have resorted to using list boxes, and using rs.Addnew etc which all works fine and allows the user to select and delete or amend. I know I will tell them they could lose finding records if changes are not reflected in records . I might add code that runs through all relevant records to update them.

However thanks again for help