Results 1 to 5 of 5
  1. #1
    rlbroerman
    Guest

    Did you mean (Access 2003)

    I am using a combo box with the "not in list" enabled so if I type in something that is not in the list it will add it. But I would also like to have it tell me that if I have typed in something similar to what is already in the list it would show me the similar one and ask if this is what I really mean. ie I type in "goppher" and "gopher" is already in the list. It would ask "Did you mean gopher" (similar to Google) and then let me accept or deny acceptance of the proposed alternative.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Did you mean (Access 2003)

    There are some aspects of the combo box that aren't obvious when you start designing forms that use them. If you have the combo box set so that not in list is enabled, then you presumably are storing a pointer to a table where the possible choices are listed, and the bound field stored in your record is a number rather than the text you type in. If you've created a routine to add the new value to the set of permissible choices, then you could do a string comparison in VBA to see if a near match exists. Note however that the code to do that is non-trivial and involves making some decisions about what is considered a near match. Also note that the autocomplete capability of a combo box will attempt to match as you type. If this isn't where you were headed, let us know.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Did you mean (Access 2003)

    Comboboxes are not designed to work that way anyhow. By enabling Not In List, you have told it to add anything it doesn't already have. You can't tell it to do two different things at once, which is what you are suggesting. Search engines like Google are not creating data, which is what a combobox does when it adds a value to the list, so they can employ fuzzy logic and standard dictionaries to produce that kind of message. As Wendell points out, the automatch feature of the combobox is the closest non-programming approximation to what you are looking for.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Did you mean (Access 2003)

    If your combo box is bound to a table, you could create a record <Add New Record> in the table, disable Not In List, and use the following code, so you sort of force your user to search for their value, before adding a new one.

    Private Sub COMBOBOX_AfterUpdate()
    Dim strSQL As String, x As String, Response As Integer
    If Me.COMBOBOX = "<Add New Record>" Then
    x = InputBox("What value do you wish to add to the list?", "Input Value")
    strSql = "Insert Into tblTABLENAME ([FIELDNAME]) values ('" & x & "')"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded

    Me.COMBOBOX.Requery
    Me.COMBOBOX = x

    End If
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Did you mean (Access 2003)

    You don't need this - a combo box has an On Not In List event that is fired if the user types a value that is not in the list. You'll find examples if you search for NotInList in this forum.

    The problem here is to design code that tries to guess whether the user is entering a truely new value, or a misspelling of an existing item. As indicated by Wendell, that is far from trivial.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •