No Duplicate entries in a Form

[Damian]

How do I make sure there is no Duplicate Entries for a Name in a given form.
If you have 2 seperate fields of Last Name and First name.

Do I build a Querie to check for that? If so, how would this look like?

Do I need to specify in a form in window Events to run the querie before
update? is that how it works?

thanks

 

[Chegu Tom]

you probably want to be sure that there are no duplicate names in the
underlying TABLE. The form does not hold any data. It only presents data
stored in tables and organized by queries

In the table design. Select both name fields and designate them as the
primary key.

To look for duplicates in existing data go the query tab, select make a new
query and choose the find duplicates wizard (don't know if this wizard is
available in 2007 but you can find it in Access 2000-2003

 

[Klatuu]

Use the Form's Before Update Event to check for existing duplicates:

If Not IsNull(DLookup("[LastName]","MyTable","[LastName] = """ &
Me.txtLastName & """ And [FirstName] = """ & Me.txtFirstName & """") Then
Cancel = True
MsgBox Me.txtFirstName & " " & Me.txtLastName & " Is Alread on file"
End If

 

[John W. Vinson]

How do I make sure there is no Duplicate Entries for a Name in a given form.
If you have 2 seperate fields of Last Name and First name.

Are you QUITE SURE you want to do that? My name's John W. Vinson; once I
worked at a university where there was a Professor John W. Vinson. (It took
two years to persuade the Internal Revenue Service that I didn't owe big bucks
for his taxes).

Names *are not unique* and you should not cripple your database by pretending
that they are.

 

[Klatuu]

Rats. Why didn't I think of that. Maybe I could convince them some other
Dave Hargis owes them that $20,000.

 

[Damian]

In mt case YES. I do not want duplicates. It is for a daily report, so if a
superintendent enters a name of a worker and another enters the same one its
bad beacuse he will get paid twice.

Also to think ahead a little bit I would need to check for something else.
If the user tries to enter the same name can I make it check if the number
of workers he had is greater then what is already in the table? The greater
number has to be recorded.

to explain. There are 8 superintendents that will be using this form. Each
of them has to enter the name of a SuperForeman and how many Foreman, Workers
and Laborers they had. Sometimes on a job they switch Supers and go work
somewhere else and need few extra workers or laborers. Thats why I need only
one name of the SuperForeman and the Highest number of people under him/her.

You probably need to see my tables.
TblCrew
cCrewID
cFname
cLname
cCrewTypeID

TblJobs
jJobID
jNumberOfForeman
jNumberOfWorkman
jNumberOfLaborers

AND the table that will hold them together.
TblJobDetail
jdJobID
jdCrewID
jdWeather
jdSubcontractorsID
jdDate

Thank you Klatuu for the code,

 

[Klatuu]

Although you have a small population, it is possible you could have two John
Smiths. You need a way to differentiate between the two with the same name.
It would be better if you have a way to tell them apart so they don't get
paid twice. One way would be to use the cCrewID (assuming that is unique to
a person) to tell them apart.

 

[Damian]

Yes CrewID is a (PK) and a unique field to each person.
How would I use that?

 

[Klatuu]

You can still use the name validation, but you have to allow the user to
override the duplicate and allow it. Then when you do your payroll
processing, use the crew id to group you data rather than the name.

Here is an example using the code I sent previously to allow an override:

If Not IsNull(DLookup("[LastName]","MyTable","[LastName] = """ &
Me.txtLastName & """ And [FirstName] = """ & Me.txtFirstName & """") Then
If MsgBox (Me.txtFirstName & " " & Me.txtLastName & " Is Alread on
file", _
& vbNewLine & "Allow Duplicate Entry", _
vbQuestion+vbYesNo+vbDefaultButton2) = vbYes Then
Cancel = True
End If
End If

 

[John W. Vinson]

In mt case YES. I do not want duplicates. It is for a daily report, so if a
superintendent enters a name of a worker and another enters the same one its
bad beacuse he will get paid twice.

Certainly, you do not want to enter the same PERSON twice.

That is not identical to saying you do not want to enter the same NAME twice.

Is this the tall, blond, friendly Dave Roberts?
Or the short, brown-haired, grumpy Dave Roberts?

You need - MUST have - some other information to identify which worker is
which. I don't know your circumstances so I don't know what information that
would be, but you'll want to store a unique worker ID in the table (not the
name), and you'll need to use a combo box or some other such tool to allow you
to *unambiguously* select the right worker.