Autocomplete in Access

[dhlundy]

using Microsoft Access 2003 to create a large database that has lots of
different text field values. In these cells, the information I need to type
often repeats in separate fields. In Excel, there is an Autocomplete option
that automatically fills in text that appears to be repeating if it is in the
same column.

What I want to know is how (or if) I can turn on an Autocomplete option in
my form that works similarly to Excel so that the more tedious parts of my
database entry are sped up considerably. This could literally save me hours a
day if someone could help me.

To be clear: I am not
trying to copy the last field into the current field, I am trying to get
Access to suggest what it thinks I am wanting to type in the field based on
other things that I have typed previously.

For instance, when you have visited websites with Internet Explorer and you
want to go back to them later, all you have to do is START typing the web
address into the address bar and then a drop-down menu appears listing the
possible choices that you may want to pick from. I want this to happen, but I
want it done while I am typing information into forms in Microsoft Access
2003.

Another example: In Excel if you have typed the word Monkeys into a cell in
the Animals column, then the word Giraffe, then the word Hippo, and then
started typing in a fourth cell the letter M, Excel would automatically add
ONKEYS behind the M because it assumes that you want to type in Monkeys
again. I want my form to do THAT for me.



Thanks for any help!

 

[Ken Snell \(MVP\)]

This feature is not available in ACCESS when typing into fields during data
entry.

 

[dhlundy]

Have you ever seen a solution to this using Autoit or similar macro program?

 

[Ken Snell \(MVP\)]

No, I am not aware of any solutions. But I've never looked for one, either,
because this functionality has not been of interest to me for use in ACCESS.

 

[John Spencer]

I guess you could use comboboxes on a form that would look up the available
values and do an autocomplete. Considering the delay inherent in having to
continually update the combobox choice lists this may not be very efficient.
Of course, if the values were fairly stable you could decide to only update
the choice lists when you first open the form for input or when you choose
to execute a requery of the values on a specified event (such as a button
click).

I wouldn't want to do this with a large number of fields or with tables with
a million records, but it is feasible.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Snell \(MVP\)]

Another possibility, if the user is entering new records in a form, would be
to use programming in the form that sets the DefaultValue property of a
control to be whatever is typed into it -- using each control's AfterUpdate
event.

 

[Albert D. Kallal]

As mentioned, we really don't have all auto complete in MS access.

However we certainly do have combo boxes, and you can set those up to behave
in much the same way.

if you start typing in a combo box, and your first few characters match,
then the combo box will auto complete for you.

It turns out that in most cases, if you have repeating data, or entries in a
particular column that are being typed over and over, then a solution in a
database is to normalize your data.

For example in excel, you might be typing in the invoice number, and a
payment to that invoice. In effect you wind up typing the invoiced number
over and over during the data entry system (or using the auto complete
feature of excel to do this for you). However this is actually considered a
bad design as still are entering the data over and over. (the fact that the
process is automated does mean your making a mess of your data).

In MS access what we would do is normalize the data, annually type in the
invoice ONE time. We then use a sub form in which you type in the amounts.
At the end of day the result we will not be typing in the invoice number
over and over.

So while products like excel have auto complete, the reason why they need
this is because are not database systems, and they're very poor at working
with data.

Good normalized database designs suggest that you move that out into a
separate table and normalize your data. This also explains while the other
poster is saying that he's never needed, or cared for, or never needed auto
complete. if you understand database modeling, you'll see a huge reduction
in this requirement to have autocomplete.

This does not mean that your forms desing will not have pick lists, or have
the ability for you to type the first few characters from a list and have
that value selected. You can, and should use a combo box for that need.

A properly normalized access database will run absolute circles around an
excel sheet for data entry in terms of speed, and reducing what you actually
have to type in.

 

[david12]

As mentioned, we really don't have all auto complete inMSaccess.

However we certainly do have combo boxes, and you can set those up to behave
in much the same way.

if you start typing in a combo box, and your first few characters match,
then the combo box will auto complete for you.

It turns out that in most cases, if you have repeating data, or entries in a
particular column that are being typed over and over, then a solution in a
database is to normalize your data.

For example in excel, you might be typing in the invoice number, and a
payment to that invoice. In effect you wind up typing the invoiced number
over and over during the data entry system (or using the auto complete
feature of excel to do this for you). However this is actually considered a
bad design as still are entering the data over and over. (the fact that the
process is automated does mean your making a mess of your data).

InMSaccesswhat we would do is normalize the data, annually type in the
invoice ONE time. We then use a sub form in which you type in the amounts.
At the end of day the result we will not be typing in the invoice number
over and over.

So while products like excel have auto complete, the reason why they need
this is because are not database systems, and they're very poor at working
with data.

Good normalized database designs suggest that you move that out into a
separate table and normalize your data. This also explains while the other
poster is saying that he's never needed, or cared for, or never needed auto
complete. if you understand database modeling, you'll see a huge reduction
in this requirement to have autocomplete.

This does not mean that your forms desing will not have pick lists, or have
the ability for you to type the first few characters from a list and have
that value selected. You can, and should use a combo box for that need.

A properly normalizedaccessdatabase will run absolute circles around an
excel sheet for data entry in terms of speed, and reducing what you actually
have to type in.

My clients have asked for this more than once.

Combobox with Auto Expand set to Yes is what I use with the following
SQL in the RowSource:
SELECT <fldname> FROM <tablename> GROUP BY <fldname> HAVING <fldname>
Is Not Null ORDER BY <fldname>;
where <fldname> is the field in the Control Source of the combobox.

Then in the Form_AfterUpdate event, requery the combobox because it
can only show values already saved.

You can even do this in the table instead of a separate lookup table
which is real nice for minimizing clutter in your tables page.

One thing it doesn't do is automatically drop-down the list when user
starts typing.
This is possible but requires a public variable and a function in the
OnChange event of the combobox.
Let me know if you need that solution.

Another thing it doesn't do is allow users to change one value across
all records at once like a lookup table would provide.
But that can be dealt with using an update-type query (or RunSQL with
SQL string built in code).

Dave Stelle, Access Application Developer