Question about Access .. Auto Complete?

  • Thread starter Thread starter hrbsh97
  • Start date Start date
H

hrbsh97

Hi,

I am entering records in my Microsoft Access database .. many of the entries
are the same .. is there a way to turn on a feature such as AUTO COMPLETE? ..
not sure if that is what I am looking for .. just looking for something say
for example if I type the name of the company in a previous record it would
remember it and throw it up the next time I started to enter it ..

Many thanks in advance to all the Access experts out there :) ..
 
hrbsh97 said:
Hi,

I am entering records in my Microsoft Access database .. many of the
entries
are the same .. is there a way to turn on a feature such as AUTO
COMPLETE? ..
not sure if that is what I am looking for .. just looking for
something say
for example if I type the name of the company in a previous record it
would
remember it and throw it up the next time I started to enter it ..

Many thanks in advance to all the Access experts out there :) ..
Click to expand...

I'm very certain I don't qualify as an Access expert, but the key
combination of CTRL + ' copies data from same field in the previous
record to the field in the current record.

That being said, if you are entering large amounts of redundant data,
there is most likely an error in the design of your database. You
shouldn't need to enter the same data (company names for example) in
multiple records.

Without further information about the design (tables, fields) of your
database, I cannot suggest a better design, just suggest you may wish to
revisit your table structure.

Julie
 
There's several approaches here that can help you:

I suppose first and foremost is that for fields that do have a common
default, on your form simply set the default of that control, and that'll
help users a lot.

In your case if you want a repeat the same value type from the last record,
simply hit control +'

(that is control key, and then hit the ' key...it will copy the previous
value for you).

Another approach that works really well is for options like favorite colors
etc is to create a combo box. The advantage of using a combo box is not only
can the user type a few characters, and have the value entered for them, but
you also achieved the time honored goal of preventing bad data being entered
into your database, and this has significant ramifications for reporting
purposes when you total by a particular grouping.

If users Forced to pick from a predefined list, then they can't even make
mistakes during data entry, and this makes reporting work for better

The old adage about garbage in, garbage out rings very true in the database
world.

Another trick for some forms is I simply set the default value of the
control in the after update event..and thus for each record addded...you get
the last default. (I only suggest doing this in cases where you really do
need the same value over and over, because often then uses fail to look at
the contorl, and if they dont' want any value...there will be one..and you
make a bigger mess).

I shall also point out if you're having to enter values over and over,
then that can hint that your database is not necessarily
normalized.

For example we can have a operator who supposed to enter today's date, and
all their work, and the operator named who entered that particular record.
In a non normalized approach, you'd have a field for today's date, and the
operators name who entered that particular information. This means for the
whole day that same two piece of information will be repeated over and over.

If you have a normalized database, then the operator will have some type of
batch job number, the date of today, the operator who is entering the data,
and then all of the records entered by that operator will in
fact become a child table *related* to that task. What this means is today's
date, and the operator number will not be entered over and over.

It is for
this reason why MS access is not designed to automatically repeat
data for you over and like excel does. We use what's called
relational data modeling, or normalized data to solve this problem (in a
sense, if you have to copy the same data over and all over, it hints that
you have a bad database design).
 
In general, you should not have a need to enter the same data many times in a
properly normalized database. If you do, then you have what is commonly
referred to as an "Access spreadsheet", which is certainly not taking
advantage of the features of a modern RDBMS (Relational Database Management
Sytem).

You might want to review some of the database design papers available here:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Make sure to read the two papers by Mike Hernandez as a minimum. That said,
if you do find the need to repeat some data, you can use the shortcut key
combination CTRL+APOSTROPHE ('). Here are some articles that you may find
helpful:

Assign default values from the last record
http://allenbrowne.com/ser-24.html

Duplicate the record in form and subform
http://allenbrowne.com/ser-57.html

How to fill a record with data from a previous record automatically
in Access 2000 and 2002 and Office Access 2003
http://support.microsoft.com/?id=210236

But, before you go too hog-wild on entering duplicate data, please do spend
the time reading up on database design. You will save yourself a lot of grief
in the future by investing the time now to make sure that you understand
database normalization, at least up to third normal form.

Good Luck,

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
On a similar note I just discovered that Access will automatically fill a text field with for me.

Create a new table and add a text field, then open the table.

In the first record for the text field type "1." Use the down arrow to the next record and type "2."

Now use the down arrow again to the 3rd record and Access will automatically enter "3." You can continue adding new records this way
indefinitely.

I have never noticed this before. Was Access designed this way?

sjones
 
sjones said:
On a similar note I just discovered that Access will automatically fill a
text field with for me.

Create a new table and add a text field, then open the table.

In the first record for the text field type "1." Use the down arrow to the
next record and type "2."

Now use the down arrow again to the 3rd record and Access will
automatically enter "3." You can continue adding new records this way
indefinitely.

I have never noticed this before. Was Access designed this way?

sjones
Click to expand...

Yes, it's an annoying bug that thankfully they've removed from Access 2007.

Behaviour like that has no place in a database (but then, you should never
be working directly with the tables)
 
Back
Top