Unwanted Duplicate Records

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

OK, I have a big problem and seek the wisdom of the mighty
Microsoft Newsgroup. I have a form for adding new
individuals when they are hired into the company. I have
code forcing individuals to fill in the required fields
but if they don't enter data in the required field, focus
is set to that field but it seems to create duplicate
records, two of the same individual. How can I set it
where it deletes the individual record or keep from
creating a duplicate record for that individual. The
caveat is, there can be several people with the same last
name so I can't restrict duplicates to the last name. Any
suggestions?

Here is my code:

ElseIf (IsNull([subfrmIntro]![DateofEmployment])) = True
Then
iResponse = MsgBox("You must enter the report no later
than date for this individual.", vbOKCancel, "Report No
Later Than Date is Blank")
Forms![subfrmIntro]![DateofEmployment].SetFocus

Select Case iResponse

Case vbOK
[subfrmIntro]![DateofEmployment].SetFocus

Case vbCancel
DoCmd.Close

End Select
 
Rick,

Forget coding for a minute, and go back to basics... don't you have a
primary key in your table that would prevent this? If not, add one! Or, if
you have a social security number field in there(I assume you have one) you
can make that your PK, or at least set it to accept unique values only (no
duplicates) in table design.

HTH,
Nikos
 
Nikos,

Your right and I do. The first time around I used a poor
example, sorry for wasting your time. Lets try this
instead. After an individual is gained to the company, we
do appraisals. These appraisals are done annually so I
will have duplicate PersonnelIDs in the Active Appraisal
query. Can I write it in the Active Appraisal query under
the PersonnelID and if so, how do I do that?
-----Original Message-----
Rick,

Forget coding for a minute, and go back to basics... don't you have a
primary key in your table that would prevent this? If not, add one! Or, if
you have a social security number field in there(I assume you have one) you
can make that your PK, or at least set it to accept unique values only (no
duplicates) in table design.

HTH,
Nikos

OK, I have a big problem and seek the wisdom of the mighty
Microsoft Newsgroup. I have a form for adding new
individuals when they are hired into the company. I have
code forcing individuals to fill in the required fields
but if they don't enter data in the required field, focus
is set to that field but it seems to create duplicate
records, two of the same individual. How can I set it
where it deletes the individual record or keep from
creating a duplicate record for that individual. The
caveat is, there can be several people with the same last
name so I can't restrict duplicates to the last name. Any
suggestions?

Here is my code:

ElseIf (IsNull([subfrmIntro]![DateofEmployment])) = True
Then
iResponse = MsgBox("You must enter the report no later
than date for this individual.", vbOKCancel, "Report No
Later Than Date is Blank")
Forms![subfrmIntro]! [DateofEmployment].SetFocus

Select Case iResponse

Case vbOK
[subfrmIntro]![DateofEmployment].SetFocus

Case vbCancel
DoCmd.Close

End Select
Click to expand...


.
Click to expand...
 
Sorry, that was wrong and stupid. In the Appraisal table
I will have duplicate PersonnelIDs not in the query. Its
in the query that I'm trying to keep from having
duplicates. We can only have one active appraisal at a
time but the individual can have multiple appraisals.

Hope that makes better sense.
-----Original Message-----
Nikos,

Your right and I do. The first time around I used a poor
example, sorry for wasting your time. Lets try this
instead. After an individual is gained to the company, we
do appraisals. These appraisals are done annually so I
will have duplicate PersonnelIDs in the Active Appraisal
query. Can I write it in the Active Appraisal query under
the PersonnelID and if so, how do I do that?
-----Original Message-----
Rick,

Forget coding for a minute, and go back to basics... don't you have a
primary key in your table that would prevent this? If not, add one! Or, if
you have a social security number field in there(I
Click to expand...
assume
you have one) you
can make that your PK, or at least set it to accept unique values only (no
duplicates) in table design.

HTH,
Nikos

OK, I have a big problem and seek the wisdom of the mighty
Microsoft Newsgroup. I have a form for adding new
individuals when they are hired into the company. I have
code forcing individuals to fill in the required fields
but if they don't enter data in the required field, focus
is set to that field but it seems to create duplicate
records, two of the same individual. How can I set it
where it deletes the individual record or keep from
creating a duplicate record for that individual. The
caveat is, there can be several people with the same last
name so I can't restrict duplicates to the last name. Any
suggestions?

Here is my code:

ElseIf (IsNull([subfrmIntro]![DateofEmployment])) = True
Then
iResponse = MsgBox("You must enter the report no later
than date for this individual.", vbOKCancel, "Report No
Later Than Date is Blank")
Forms![subfrmIntro]! [DateofEmployment].SetFocus

Select Case iResponse

Case vbOK
[subfrmIntro]! [DateofEmployment].SetFocus

Case vbCancel
DoCmd.Close

End Select
Click to expand...


.
Click to expand...
.
Click to expand...
 
So you could have a boolean Yes/no field in your Appraisals table, set the
active appraisal to Yes, amd all the others to no. Then filter the query to
just show the Yes values.
Alternately, if the Appraisal table contains the date, and you just want the
last appraisal active, sort the query by date, and show the TOP value.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Rick said:
Sorry, that was wrong and stupid. In the Appraisal table
I will have duplicate PersonnelIDs not in the query. Its
in the query that I'm trying to keep from having
duplicates. We can only have one active appraisal at a
time but the individual can have multiple appraisals.

Hope that makes better sense.
-----Original Message-----
Nikos,

Your right and I do. The first time around I used a poor
example, sorry for wasting your time. Lets try this
instead. After an individual is gained to the company, we
do appraisals. These appraisals are done annually so I
will have duplicate PersonnelIDs in the Active Appraisal
query. Can I write it in the Active Appraisal query under
the PersonnelID and if so, how do I do that?
-----Original Message-----
Rick,

Forget coding for a minute, and go back to basics... don't you have a
primary key in your table that would prevent this? If not, add one! Or, if
you have a social security number field in there(I
Click to expand...
assume
you have one) you
can make that your PK, or at least set it to accept unique values only (no
duplicates) in table design.

HTH,
Nikos

OK, I have a big problem and seek the wisdom of the mighty
Microsoft Newsgroup. I have a form for adding new
individuals when they are hired into the company. I have
code forcing individuals to fill in the required fields
but if they don't enter data in the required field, focus
is set to that field but it seems to create duplicate
records, two of the same individual. How can I set it
where it deletes the individual record or keep from
creating a duplicate record for that individual. The
caveat is, there can be several people with the same last
name so I can't restrict duplicates to the last name. Any
suggestions?

Here is my code:

ElseIf (IsNull([subfrmIntro]![DateofEmployment])) = True
Then
iResponse = MsgBox("You must enter the report no later
than date for this individual.", vbOKCancel, "Report No
Later Than Date is Blank")
Forms![subfrmIntro]! [DateofEmployment].SetFocus

Select Case iResponse

Case vbOK
[subfrmIntro]! [DateofEmployment].SetFocus

Case vbCancel
DoCmd.Close

End Select



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top