Customize auto-complete functionality on Microsoft Access datasheet

Question

I have a main form tied to a User record, with a subform tied to a number of Client objects the user "owns." So, there is a one-to-many relationship between User and Client.

As the subform exists, the user can add, remove, and edit entries in the Clients subform. When a user adds an entry to the subform datasheet, there is an autocomplete functionality that kicks in the user types part of a Client name that matches any names in the Client database, thus saving the user a few keystrokes and ensuring that the user enters a name that exactly matches one in the Client database.

One thing to note with the Clients table is that in addition to each Client having a unique numerical ID, each Client has a full company name (Test Agency, Inc.), a colloquial name (Test Agency) and an abbreviated name (TA).

I am trying to edit the subform so that the autocomplete functionality will match against any of the three fields listed above (full name, colloquial name, and abbreviated name). Right now, the autocomplete only works against the full name, as that is the field linked to the subform. I would like the user to be able to type in a part of a string, the subform to try and match it to any of the three fields (full name, colloquial name, abbreviated name) and return a list of potential matches to any of the three fields. When the user selects the correct potential match for the Client they are trying to search for, then then full company name would be entered into the datsheet. Basically, these additional fields just make it easier for the user to find the Client they are looking for (imagine typing in AMD instead of Advanced Micro Devices, Inc.)

My first question--is this possible to do with a simple datasheet? I've looked into using lookup fields and multi-value lookup fields, but I'm not sure this is the right method. Or will I need to build myself a custom control that does this matching on multiple fields?

Answer 1

Made a query like this

SELECT *
FROM Company
WHERE fullName LIKE '*' & pCompany & '*'
    OR Colloquial LIKE '*' & pCompany & '*'
    OR Abbr LIKE '*' & pCompany & '*'

and on my form I did this

Private Sub cboCompany_KeyUp(KeyCode As Integer, Shift As Integer)
    ClearCombo cboCompany

    Dim sql As String

    Dim rs As DAO.Recordset
    Dim companySearch As DAO.QueryDef
    Set companySearch = CurrentDb.QueryDefs("CompanySearch")
    companySearch.Parameters("pCompany") = cboCompany.Text

    Set rs = companySearch.OpenRecordset

    Do While Not rs.EOF
        cboCompany.AddItem rs("ID") & ";" & rs("FullName") & ";" & rs("Colloquial") & ";" & rs("Abbr")
        rs.MoveNext
    Loop
End Sub

Private Sub ClearCombo(cbo)
    For i = cbo.ListCount - 1 To 0 Step -1
            cbo.RemoveItem i
    Next i
End Sub

It's not super fast at all but it works. I think what would make it faster is not cuing off the KeyUp event and instead on a timer once users start typing in that field. Then turn the timer off when they stop typing or focus leaves the combobox.