Help with Access forms

N

noncentric

I taught myself enough Access today to create 4 tables and a couple forms,
which will populate the tables the way I need them to be populated.

Now I'm stuck...

I have firstname's and lastname's for each person in my db, along with
additional info. I assigned a unique id to each person, and this unique id is
used to link the various tables.

I have a form that will allow me to enter new people (who will then
automatically receive a unique id). The form also allows me to edit existing
people's records.

However, I don't want to have multiple records for the same person. I need a
way to determine whether people I'm entering are already in the db (and
therefore don't need a new ID)...or if they're new to the db (and therefore
do need a new ID).

It seems that the only solution is for me to go through the list of people
I'll be entering, and compare them to a list of people already in my db. This
will take too much time.

Ideally, I'd like the form to work as follows:
I enter firstname, then enter lastname, then:
- the form gives me an error message if that firstname/lastname combination
is already present in my db...and even better, the form auto-populates with
the existing data for that person so I only have to edit one or two fields.
- if the person isn't already in my db, then I'll go ahead and complete all
of the fields for that person.

If anyone has any advice, then that would be greatly appreciated. If this
isn't at all possible, then that info would be good to know. ;)

Thanks!
 
E

Eric Blitzer

I think you are starting in the wrong direction. What do you doe if two
people have the same first an last name
John smith
Jose Martinez

You need another field to make that individual unique
address
phone number
ss# (idon't like using that)
.......

Chris
 
N

noncentric

Hi Eric, thanks for the reply. There are already several instances of
multiple phone numbers, addresses, etc. There are several instances of
individuals from the same households in my db. And I don't have SSN, so
that's not an option.

I figured that if there are people with the same first and last names, then
I'd just add their middle initial (as part of their "firstname"). Or add a 1
or 2 at the end of their "lastname".

I'm mostly stuck on figuring out how to get the form to autopopulate and/or
alert me if there's a duplicate entry.

Thanks!
 
T

Tom Wickerath

Since you are learning Access, now would be a good time to visit a local book
store. Browse through the collection of Access books. The feature you are
looking for is commonly known as the Soundex function. It requires using VBA
code to implement it. You will likely find this discussed in the developer
type books, not the basic intro. type books.

The nice thing about using the Soundex function is that it can help you
catch duplicates even if they are not spelled exactly the same. It will allow
you to find
matching names, as well as phonetically similar matches such as:

Tom Wickerath vs. Thom Wickerath vs. Thomas Wickerrath
(notice the double "r" in the last [incorrect] spelling of my name)

and

John vs. Jhon (when combined with a reasonably close spelled last
name).

The code is available, among other places, in the book:

"VBA Developer's Handbook, 2nd Edition"
by Ken Getz and Mike Gilbert
Sybex, Inc.


Tom
________________________________________

:

I taught myself enough Access today to create 4 tables and a couple forms,
which will populate the tables the way I need them to be populated.

Now I'm stuck...

I have firstname's and lastname's for each person in my db, along with
additional info. I assigned a unique id to each person, and this unique id is
used to link the various tables.

I have a form that will allow me to enter new people (who will then
automatically receive a unique id). The form also allows me to edit existing
people's records.

However, I don't want to have multiple records for the same person. I need a
way to determine whether people I'm entering are already in the db (and
therefore don't need a new ID)...or if they're new to the db (and therefore
do need a new ID).

It seems that the only solution is for me to go through the list of people
I'll be entering, and compare them to a list of people already in my db. This
will take too much time.

Ideally, I'd like the form to work as follows:
I enter firstname, then enter lastname, then:
- the form gives me an error message if that firstname/lastname combination
is already present in my db...and even better, the form auto-populates with
the existing data for that person so I only have to edit one or two fields.
- if the person isn't already in my db, then I'll go ahead and complete all
of the fields for that person.

If anyone has any advice, then that would be greatly appreciated. If this
isn't at all possible, then that info would be good to know. ;)

Thanks!
 
T

Tom Wickerath

Also, check out page 16 of this free sample of Smart Access. Here, you will
find a discussion of Soundex and the Levenshtein Distance (LD) algorithm,
written by Access MVP Doug Steele:

http://www.smartaccessnewsletter.com/Media/MediaManager/SA_Sample.pdf

Tom
______________________________________________

:

Since you are learning Access, now would be a good time to visit a local book
store. Browse through the collection of Access books. The feature you are
looking for is commonly known as the Soundex function. It requires using VBA
code to implement it. You will likely find this discussed in the developer
type books, not the basic intro. type books.

The nice thing about using the Soundex function is that it can help you
catch duplicates even if they are not spelled exactly the same. It will allow
you to find
matching names, as well as phonetically similar matches such as:

Tom Wickerath vs. Thom Wickerath vs. Thomas Wickerrath
(notice the double "r" in the last [incorrect] spelling of my name)

and

John vs. Jhon (when combined with a reasonably close spelled last
name).

The code is available, among other places, in the book:

"VBA Developer's Handbook, 2nd Edition"
by Ken Getz and Mike Gilbert
Sybex, Inc.


Tom
________________________________________

:

I taught myself enough Access today to create 4 tables and a couple forms,
which will populate the tables the way I need them to be populated.

Now I'm stuck...

I have firstname's and lastname's for each person in my db, along with
additional info. I assigned a unique id to each person, and this unique id is
used to link the various tables.

I have a form that will allow me to enter new people (who will then
automatically receive a unique id). The form also allows me to edit existing
people's records.

However, I don't want to have multiple records for the same person. I need a
way to determine whether people I'm entering are already in the db (and
therefore don't need a new ID)...or if they're new to the db (and therefore
do need a new ID).

It seems that the only solution is for me to go through the list of people
I'll be entering, and compare them to a list of people already in my db. This
will take too much time.

Ideally, I'd like the form to work as follows:
I enter firstname, then enter lastname, then:
- the form gives me an error message if that firstname/lastname combination
is already present in my db...and even better, the form auto-populates with
the existing data for that person so I only have to edit one or two fields.
- if the person isn't already in my db, then I'll go ahead and complete all
of the fields for that person.

If anyone has any advice, then that would be greatly appreciated. If this
isn't at all possible, then that info would be good to know. ;)

Thanks!
 
G

George Nicholson

Add 1 new Index to your table comprised of *both* the FirstName + LastName
fields. Set it to NoDuplicates.

Then, when you try to enter a duplicate FirstName+LastName combination,
Access won't let you.
 
J

John Vinson

Add 1 new Index to your table comprised of *both* the FirstName + LastName
fields. Set it to NoDuplicates.

Then, when you try to enter a duplicate FirstName+LastName combination,
Access won't let you.
Click to expand...

True... but what if you wanted to enter my two former coworkers, Dr.
Lawrence David Wise and his colleague, Dr. Lawrence David Wise? Or for
that matter, my two friends Fred Brown (the greying stocky friendly
guy) and Fred Brown (the lively wisecracking high school junior)?

Far better to *check* for duplicates and give the user a choice,
rather than blocking what should be an allowed action!

John W. Vinson[MVP]
 
Top