advanced autocomplete (like in Firefox)

[Ornitobase]

Hi,
my database allows the user to store general information about his
trip (MainForm) and add bird observations through a datasheet-like
subform (SubForm).
Currently the new bird is inserted through a combobox with
autocomplete, bound to the table Species, but this process is
annoying. I.e. if I want to insert the sight of a Starling, I must
remember to start writing "C..o..m..mon Starling"; if I start writing
"S..t.." I get only "Striated Grasswren" etc.
How can I show in the dropdown menu all the bird names that contain
the written letters (no matter if at the beginning, middle or end of
the name?).

Thanks in advance, I look forward to a solution!

 

[Allen Browne]

The combo's autocomplete in Access cannot do what you want, i.e. it will
only select based on the leading characters in the field.

It would be possible to write code to make a text box behave the way you
describe. Use its Change event to examine its Text (not its Value), replace
its value with the first matching record (using leading and trailing
wildcards to get the match.)

Having done that, the entire Text will be selected so the next keystroke
will wipe it all out. You will need to use module-level variables to keep
track of what was selected before this change, and locate those characters
(which could occur in muliple places) and select the right ones. You will
have to code this to correctly handle inserting characters earlier in the
string (not just at the end), as well as deleting or backspacing characters
anywhere in the string (which could give you a completely different match.)

In the end, I'm not sure that this interface is going to work well for you.
It might be easier to put a command button alongside the control, and pop up
a continuous form to let the user find the desired record and insert it into
your original form. The target form could let you find the bird by filtering
on any of the fields (species, common name, whatever.)

For an example of how to build a search form like that, see:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

HTH