I have a table, Counties, with one field, PropertyCounties. There is a combobox on the form, Orders, with the table Counties as its source. The limit to list is set to yes. When the user enters a value not on the list--let’s call it “ aLpha ”--the not on list event procedure takes over. The entry is run through a combobox cleanup function, changing it to “Alpha”. I then am trying to use DLookup to check to see if, in fact, “Alpha” is not already on the list. (Obviously, “ aLpha” would not have been recognized by Access.) When I run this code through the debugger, the DLookup is finding values in the Counties table, but they are apparently random valid values, e.g., “Beta”.

I want DLookup to tell me if there is already an entry in the Counties table (e.g., Alpha) that the combobox cleanup function returns after the user’s input (e.g., Alpha).

How do I do this?

Code:
Private Sub PropertyCounty_NotInList(NewData As String, _
    Response As Integer)
Dim strNewCounty As String
Dim varTemp As Variant
varTemp = DLookup("[PropertyCounties]", "Counties", _
    "[PropertyCounties]=" & (strNewCounty = ComboboxCleanUp(NewData)))
If strNewCounty = varTemp Then
    Me.Undo
    MsgBox "This county is already in the list.", _
    vbOKOnly, "Duplicate County Found"
Exit Sub

ElseIf MsgBox("Add " & strNewCounty & " to list?", vbYesNo, "Add a new county?") = vbYes Then
    Dim db As Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Counties")
    rst.AddNew
    rst!PropertyCounties = strNewCounty
    rst.Update
    Response = acDataErrAdded
    rst.Close
    Me.PropertyCounty.Value = strNewCounty
    Me.PropertyCounty.RowSource = Me.PropertyCounty.RowSource
'Sometimes the requery does not work so I use the line above
'for a forced requery
    Response = acDataErrContinue
Else
     Response = acDataErrContinue
     Me.Undo
     MsgBox "Use a county that is already in the list.", _
     vbOKOnly, "A new county will not be added."
End If
    End Sub

Public Function ComboboxCleanUp(ByVal strTemp As String) As String
'Used with all comboboxes on control
        strTemp = LTrim(RTrim(strTemp))
'Leading and following spaces stripped
        strTemp = StrConv(strTemp, vbProperCase)
'First letter capitalized
        ComboboxCleanUp = strTemp
'Cleaned-up string replaces old string
End Function