Add item to combo box list

[JeffreyDavid]

I have a combo box based on a Table/Query. I want to add an item to the existing list so next time the form is open, the new item will be available from the list. I have added the following code to the NotInList event but keep getting an error message saying 'The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items.' I have the LimitToList = yes My NotInList event code is:

Private Sub cmbDescription_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available description Name" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new description to the current Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrAdded
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SalesProduct", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!DESCRIP = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Can anyone see why this wouldn't work?

 

[WayneRyan]

Jeffrey,

If you Limit them to the List, then how can they enter a value
that is not on the list?

LimitToList = No

Wayne

 

[Mile-O]

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

    On Error Goto Err_ErrorHandler

    Const NL = vbCrLf & vbCrLf

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strMsg As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SalesProduct", dbOpenDynaset)

    strMsg = "'" & NewData & "' is not an available description Name" & NL
    strMsg = strMsg & "Do you want to associate the new description to the current Table?"
    strMsg = strMsg & NL & "Click Yes to link or No to re-type it."

    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        With rs
            .AddNew
            .Fields("DESCRIP") = NewData
            .Update
            .Close
        End With
        Response = acDataErrAdded
    End If

Exit_ErrorHandler:
    Set rs = Nothing
    Set db = Nothing

Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

End Sub

 

[Mile-O]

If you Limit them to the List, then how can they enter a value
that is not on the list?

The NotInList event allows this to be handled.

 

[JeffreyDavid]

When I have the LimitToList = no, I don't get any error messages and it doesn't add the item to the list. If I have LimitToList = yes, I get an syntax error on the line Response = acDateErrContinue and it still doesn't add it to the list.

 

[Mile-O]

That's a common typo of mine...must learn to check these things when I type out code.

[b]Response = acDataErrContinue[/b]

 

[JeffreyDavid]

It works!

Thanks everyone!

 

[JeffreyDavid]

I didn't think it was supposed to be this hard! Well it is not working again! I didn't change anything, I double checked and then completely rebuilt the process. Yet still no addidtions.
I'm not getting any popup messages or anything.

Does anyone have any suggestions?

 

[a.sinatra]

i did it the cheap way

add the value in your table...

Private Sub artist_AfterUpdate()
    If field = " New Value?" Then
        DoCmd.OpenForm "frmName", acNormal, "", ""
    End If
End Sub

i used the space cause it puts it first in the combo box
________
Ford Model N History

 

[Mile-O]

Well it is not working again!

I can see absolutely nothing wrong with the code I rewrote above. And you are saying it doesn't write to your table called SalesProduct?

Do you have other things such as forms, reports, queries, etc. called SalesProduct also? If so, this might be one of those occasions where best practice comes in - the best practice being to start using naming conventions in your database.

 

[Mile-O]

DoCmd.OpenForm "frmName", acNormal, "", ""

You don't need those Null Strings, a.sinatra.

DoCmd.OpenForm "frmName", acNormal

is fine on its own.

Of course, if you are doing what I think you're doing. (opening the data entry form for something that's not in a combo) then you can use the acFormAdd argument so that it opens the form on a fresh record.