Check data for duplicates as you type (access 2003)

[loosehelm]

Is there a way to type a name into a form and have it automatically pull up
matching records without having to leave the field or click a control? My
problem is that I have so far about
1700 records, (I started with about 3500), and the form that my co-workers
use to input or update Records does not let them know if they are duplicating
an entry that is already there...
I would like to have my co-workers fill in the "LastName" field on my form
and have a display on the bottom of the form or as a pull down box listing
the closest match and the three records after it. That way they could just
click on the record that is already there and update it instead of creating a
new record if needed. There is also 1 more thing that might be important,
the DB is kept on a shared drive and about 5 people can access it at any time.

When I was hired I thought because I had some basic skills with Access, I was
going to be trained on a DB under the tutelage of someone senior. I did not
know that I was going to have to re-create a substantial DB from scratch! I
am a beginning user but I fear that what some of the things my boss wants me
to do would fall under the abilities of an advanced user. If someone could
suggest some books that are useful I would love the help in that area also.
thanks, Jerry

 

[Minton M]

Is there a way to type a name into a form and have it automatically pull up
matching records without having to leave the field or click a control? My
problem is that I have so far about
1700 records, (I started with about 3500), and the form that my co-workers
use to input or update Records does not let them know if they are duplicating
an entry that is already there...
I would like to have my co-workers fill in the "LastName" field on my form
and have a display on the bottom of the form or as a pull down box listing
the closest match and the three records after it. That way they could just
click on the record that is already there and update it instead of creating a
new record if needed. There is also 1 more thing that might be important,
the DB is kept on a shared drive and about 5 people can access it at any time.

When I was hired I thought because I had some basic skills with Access, I was
going to be trained on a DB under the tutelage of someone senior. I did not
know that I was going to have to re-create a substantial DB from scratch! I
am a beginning user but I fear that what some of the things my boss wants me
to do would fall under the abilities of an advanced user. If someone could
suggest some books that are useful I would love the help in that area also.
thanks, Jerry

Yes... the OnKeyPress event of a field will fire and you can check the
Text property to see what is currently there. You can then run a query
to match against your database BUT... performance is likely to be
impacted by this approach unless you can cache all the data in some in-
memory object (Dictionary, etc.). Much easier - you could use a combo
to match and then if NotInList fires, carry on as normal. Access
Combo's can carry serious quantities of data and the matching is
faster than any VBA subterfuge.

Hope this helps,
James