Ms access: Autocomplete field with values from another table

Question

Hello, please forgive me for my poor english and my big ignorance on programming.

I'm using Ms Access 2003.

Let's suppose i have two tables:

Table1: ID (autonumber), [...], Keywords (memo)

Table2: ID (autonumber), Keyword (text)

I want: 1) As the user types letters in Table1.Keywords that my database searches in Table2.keyword for the nearest value and proposes it by autocompleting (just like google proposes a search word as you type)

2) When user presses ", " that he can add one more keyword in the same field (and the autocomplete still runs for this next value)

3) If he types a keyword not included in Table2 and press ", " that he is asked if he wants this value to be added in Table2

Well, i'm not sure if all these are clear... maybe they are a lot of things...

But i'd appreciate if you could help me...

Thanks in advance J.

Answer 1

Why not use a "Combo Box" and set its Row Source Type to Table/Query, and then make the Row Source a query on the second table. Just make sure you don't turn on Limit to List.

Answer 2

CodeSlave mentions a way that will work. But it will only work for one value. There is no way to do the multi-words-separated-by-commas thing. Only one word at a time.

As for the Adding new values. The combobox control support an OnNotInList event which can do what you say.

Seth

Answer 3

It would be complicated to do it with a single control, but with two controls, a dropdown list for choosing the value to add, and a textbox displaying the memo field, you could have the combo box's AfterUpdate event append a comma and the chosen value to the existing data. Something like this:

  Private Sub cmbChooseKeyword_AfterUpdate()
    If Not IsNull(me!cmbChooseKeyword) Then
       Me!txtKeywordMemo = (Me!txtKeywordMemo + ", ") & Me!cmbChooseKeyword
    End If
  End Sub

You'd also want the rowsource of your combo box to not list items that are already entered, so this is one way that would work for a relatively short list of keywords:

  SELECT tblKeywords.*
  FROM tblKeywords
  WHERE InStr(Forms!MyForm!txtKeywordMemo, tblKeywords.Keyword) = 0;

Then you'd add:

  Me.Dirty = False
  Me!cmbChooseKeyword.Requery

...at the end of the AfterUpdate code above (inside the End If):

  Private Sub cmbChooseKeyword_AfterUpdate()
    If Not IsNull(me!cmbChooseKeyword) Then
       Me!txtKeywordMemo = (Me!txtKeywordMemo + ", ") & Me!cmbChooseKeyword
       Me.Dirty = False
       Me!cmbChooseKeyword.Requery
    End If
  End Sub

...and you'd want to add the requery to the OnCurrent event of your form, as well (so that when you arrive on a record, the combo box already omits any keywords that are already in the list).

Now, all that said, I'd completely recommend against doing this. This is a denormalized way to store the data, and this leads to problems:

1. what if you want to delete one keyword?

2. what if you want the keywords to be sorted in alphabeticsal order?

3. what if you have 100s of thousands of records and you want to search this field with LIKE "*Keyword*" -- will it bog down to be terribly slow (no indexes, and not used well even if there were)?

You really should use a proper many-to-many structure, with an additional table between the one where you're currently storing the keyword memo and your keyword list table. This "joins" the two, and would then give you a list.

You could then use a subform with a dropdown list to populate each row of the join table.

If you like presenting the keywords on reports as a comma-separated list (as you're currently storing them), you can write a simple function to do the concatenation for you at the presentation layer of your reports (concatenation functions for that purpose are a frequent Access question here on Stackoverflow).