looking for auto-fill

[Kmarie]

I am typing in hundreds of names and addresses, is there a function that will
automatically fill in.... if I start typing Madison that it would fill in
Madison when I got to Ma...? This would save me hundreds of key strokes.
--
kmarie

[John W. Vinson]

On Wed, 23 Dec 2009 13:25:01 -0800, Kmarie <Kma...@discussions.microsoft.com>
wrote:

>I am typing in hundreds of names and addresses, is there a function that will
>automatically fill in.... if I start typing Madison that it would fill in
>Madison when I got to Ma...? This would save me hundreds of key strokes.

Into what are you typing them? If you're using a Form in a Microsoft Access
database (the subject of this group... you don't say...) you can use a Combo
Box bound to the field. It will autocomplete based on what you type.

More info please!

--

John W. Vinson [MVP]

[KenSheridan via AccessMonster.com]

I assume you are referring to something like street or city names, here e.g.
Madison Avenue, New York. This is really a database design issue, by which I
mean a question of the tables and the relationships between them which make
up the 'logical model' of the database as a whole. Lets assume for this
example that the database is one which includes a table Customers, and you
need to add the customer addresses, what you need a re a set of correctly
'normalized' tables. Normalization is a formal process which eliminates
redundancy in a database. This is important not simply for efficiency, but
more so because it eliminates the risk of inconsistent data. Redunncy would
be if we were told in multple rows in the table that Madison Avenue is in
NewYork and that New York is in NY state.

The way redundancy is eliminated, and at the same time data entry made easier
in the way you describe, is by 'decomposing' a table into related tables. In
this case you'd have a table Customers, which would include a column
StreetID; not StreetName note, because these can be legitimately duplicated,
so each street needs a unique numeric ID.

The Customers table would not have a City or State column, however, because
its StreetID column would reference the StreetID primary key of a Streets
table, which would have columns StreetName and CityID, the latter being a
foreign key which would reference a Cities table with columns CityID, City
and State, the latter referencing the primary key of a States table. So the
tables and theror relationships would look like this diagrammatically:

Customers>----Streets>----Cities>----States

i.e. there would be a one-to-many relationship between each.

When entering a customer in a form you'd have a combo box bound to the
Streets table. Its RowSource would be:

SELECT StreetID, Street, City, State
FROM Streets INNER JOIN Cities
ON Streets.CityID = Cities.CityID
ORDER BY Street, City, State;

Its other properties would be set up something like this:

ControlSource: StreetID
BoundColumn: 1
ColumnCount: 4
ColumnWidths: 0cm;3cm;3cm;1cm
ListWidth: 7cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column. Experiment with the other dimensions to
get the best fit. The ListWidth is the sum of the column widths.

The combo box's AutoExpand property True(Yes). This means that as you begin
to type a street name the control will progressively find the first match in
the list, which is essentially what you were asking.

The above probably won't match your model precisely of course; it may be
nothing to do with streets, cities etc at all. Its actually unusual in my
experience to have a streets table in most day-to-day databases, which mostly
will have a CityID referencing the Cities table, but include the street names
in the general address columns. However, if you did have many records which
repeated street names it would be appropriate to normalize the table to this
level. Here in the UK in fact its common just to store the post code as
these give quite a precise location; mine maps to one side of my street for
instance. But whatever your database is actually modelling the principles
are the same, and you'd normally achieve your aim of reduced typing in this
way, but really that's just a beneficial side product of the real purpose of
normalization which is ensuring data integrity.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com