Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Completion (2003)

    Is it possible to get Access to auto complete a field in the way that Excel does once it finds uniqueness in the characters being typed?
    That is, in Excel, if one has already typed in the word Expenses in a column, when you type E in the same column of another row, Excel offers the word Expenses as soon as the E is typed, as long as there isn't another word beginning with E in the same column. (The user is getting fed up with typing the names of towns in volunteer records; I can't see how to set up a drop down list when it's not known where these volunteers might come from).
    Thanks
    Silverback
    Silverback

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

    Re: Auto Completion (2003)

    You can set up a table of town names that is to be filled by Access as the users type in names.
    Create a combo box on the form bound to the town field, with the towns table as row source.
    Set the Limit to List property of the combo box to Yes.
    Write code in the On Not In List event of the combo box to add new names to the table. Here is an example. You'll have to substitute the correct names, obviously:
    <code>
    Private Sub cboTown_NotInList(NewData As String, Response As Integer)
    If MsgBox("The town " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblTowns (Town) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    Me.cboTown.Undo
    Response = acDataErrContinue
    End If
    End Sub
    </code>
    The message box is intended to give the user a chance to correct typos (you wouldn't want Warington, Warrngton etc. as well as Warrington in your table).
    As the table grows, autocomplete will become more and more useful.

    Note: the code requires a reference to the Microsoft DAO 3.6 Object Library.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Completion (2003)

    As an alternative to creating a new table, you can just use the values from the existing field as the row source for a combobox. For example, if you had a tblClient table that contained a City field, your rowsource would be "SELECT DISTINCT CITY FROM tblClient". You can still have code that would ask the user if it was OK to accept an entry that was not already in the combobox.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Completion (2003)

    Many thanks, Hans and Mark.
    Just what I was looking for.
    Silverback
    Silverback

Posting Permissions

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