combobox not in list

[james_lankford]

so let's say I have a combobox that is getting its data from a table of Colors

the table contains
Red
Green
Blue

so the combobox shows those values
the user goes to the combobox and enters Purple
the notinlist event gets triggered, I pop up a message, they say yes, I insert Purple into the Colors table and all is well
later the user goes to the combox and enters "______Purple"
no quotes, no underscores, I'm just showing that they're entering a bunch of spaces followed by some text
well, " Purple" is not in the colors table,
so the notinlist event gets triggered, I pop up a message, they say yes, I insert " Purple" into the Colors table and all is not well
I do not want " Purple" in the table
as far as I'm concerned its a duplicate of Purple
I also do not want a new entry of "____________________Orange"
but I cannot figure out how to do this

surely I can't be the first one with this problem, anyone got any ideas ?

 

[JonXL]

Maybe something like this... ? (Based on Micron's suggestion of the DLookUp.)

Private Sub cmbColor_NotInList(NewData As String, Response As Integer)
   
    ' from http://www.databasedev.co.uk/not_in_list.html
   
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
   
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
   
    If DCount("color", "color", "[color] = '" & Trim(NewData) & "'") > 0 Then
   
        Me.cmbColor.Value = Trim(NewData)
        Exit Sub
   
    End If
   
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
   
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
    If i = vbYes Then
        strSQL = "Insert Into color ([color]) " & _
                 "values ('" & Trim(NewData) & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
   
End Sub

I would be careful with these kind of lookups, though, when using strings like that. This Google search should provide plenty of reading on ways of working around that.

access vba sql apostrophe - Google Search

I have a function called CSql() that I use for this purpose. I can maybe post it if this is something you'd be interested in using. Or you can come up with your own solution on that if you want.