Preventing Duplicate Entries on a Continuous Subform

[DoveArrow]

I have a form with a continuous subform that lists all of the academic
programs that an advisor for a particular campus works on.
Unfortunately, when I was building the database, I didn't realize that
there was a fairly decent chance that a duplicate academic program
could be created for a particular advisor. I don't want to go back and
rebuild the database, so what I want to do is run a routine of some
kind that will check to see if any of the new programs added to the
list are duplicates. If they are, I want a message to pop up that says
"This record is a duplicate record, and will not be added." I then
want it to delete the duplicate record.

Unfortunately, I can't think of a good way to do this. Does anyone
have any suggestions?

 

[Roger Carlson]

Not sure what you're asking. If it's to delete already existing duplicate
entries, you might look on my website (www.rogersaccesslibrary.com), for a
small Access database sample called "RemoveDuplicates.mdb" which illustrates
how to do this. If you just want to prevent any new duplicates from being
created, you need to create a Unique Index on some field or combination of
fields in your subform.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DoveArrow]

Not sure what you're asking.  If it's to delete already existing duplicate
entries, you might look on my website (www.rogersaccesslibrary.com), for a
small Access database sample called "RemoveDuplicates.mdb" which illustrates
how to do this.   If you just want to prevent any new duplicates from being
created, you need to create a Unique Index on some field or combination of
fields in your subform.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L







- Show quoted text -

I think your Unique Index idea might be the way to go, but I'm not
sure how to create it, because it involves two, separate fields. The
first field has what's called an Advisor ID. The second field has
what's called an Academic Program ID. You can have multiple duplicates
of the Advisor ID, and multiple duplicates of the Academic Program ID,
but only one Academic Program ID per Location Advisor ID. I know that
sounds a little complicated, so maybe a visual would help.

AdvisorID | Academic Program ID
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
4 | 1
4 | 2
4 | 3

It's basically a join table, with information being pulled from two
other tables. However, I don't want duplicate information where the
Advisor ID has two of the same Academic Program ID. Again, here's a
visual, only this is an example of what I don't want.

Advisor ID | Academic Program ID
1 | 2
1 | 2

I hope this makes sense. Let me know if you have any questions still.

 

[DoveArrow]

Not sure what you're asking.  If it's to delete already existing duplicate
entries, you might look on my website (www.rogersaccesslibrary.com), for a
small Access database sample called "RemoveDuplicates.mdb" which illustrates
how to do this.   If you just want to prevent any new duplicates from being
created, you need to create a Unique Index on some field or combination of
fields in your subform.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L







- Show quoted text -

I think your Unique Index idea might be the way to go, but I'm not
sure how to create it, because it involves two, separate fields. The
first field has what's called an Advisor ID. The second field has
what's called an Academic Program ID. You can have multiple duplicates
of the Advisor ID, and multiple duplicates of the Academic Program ID,
but only one Academic Program ID per Location Advisor ID. I know that
sounds a little complicated, so maybe a visual would help.

AdvisorID | Academic Program ID
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
4 | 1
4 | 2
4 | 3

It's basically a join table, with information being pulled from two
other tables. However, I don't want duplicate information where the
Advisor ID has two of the same Academic Program ID. Again, here's a
visual, only this is an example of what I don't want.

Advisor ID | Academic Program ID
1 | 2
1 | 2

I hope this makes sense. Let me know if you have any questions still.

 

[BruceM]

Open the table in design view. Click View >> Indexes. Give your index a
name in the left column, select a field in the middle column, and choose a
sort order in the right column. Select the other field in the next row of
the dialog box, and choose a sort order, but do not add an index name.
Click the index name, and choose Unique in the bottom left part of the
dialog box. If the combination of fields is the primary key you can select
that option, but remember that you will need a two-field foreign key for any
relationships.
These instructions assume Access 2003 or earlier. I expect the process is
similar in Access 2007, but I don't know exactly how the dialog works.

Not sure what you're asking. If it's to delete already existing duplicate
entries, you might look on my website (www.rogersaccesslibrary.com), for a
small Access database sample called "RemoveDuplicates.mdb" which
illustrates
how to do this. If you just want to prevent any new duplicates from being
created, you need to create a Unique Index on some field or combination of
fields in your subform.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L







- Show quoted text -

I think your Unique Index idea might be the way to go, but I'm not
sure how to create it, because it involves two, separate fields. The
first field has what's called an Advisor ID. The second field has
what's called an Academic Program ID. You can have multiple duplicates
of the Advisor ID, and multiple duplicates of the Academic Program ID,
but only one Academic Program ID per Location Advisor ID. I know that
sounds a little complicated, so maybe a visual would help.

AdvisorID | Academic Program ID
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
4 | 1
4 | 2
4 | 3

It's basically a join table, with information being pulled from two
other tables. However, I don't want duplicate information where the
Advisor ID has two of the same Academic Program ID. Again, here's a
visual, only this is an example of what I don't want.

Advisor ID | Academic Program ID
1 | 2
1 | 2

I hope this makes sense. Let me know if you have any questions still.

 

[DoveArrow]

Open the table in design view.  Click View >> Indexes.  Give your index a
name in the left column, select a field in the middle column, and choose a
sort order in the right column.  Select the other field in the next row of
the dialog box, and choose a sort order, but do not add an index name.
Click the index name, and choose Unique in the bottom left part of the
dialog box.  If the combination of fields is the primary key you can select
that option, but remember that you will need a two-field foreign key for any
relationships.
These instructions assume Access 2003 or earlier.  I expect the process is
similar in Access 2007, but I don't know exactly how the dialog works.







I think your Unique Index idea might be the way to go, but I'm not
sure how to create it, because it involves two, separate fields. The
first field has what's called an Advisor ID. The second field has
what's called an Academic Program ID. You can have multiple duplicates
of the Advisor ID, and multiple duplicates of the Academic Program ID,
but only one Academic Program ID per Location Advisor ID. I know that
sounds a little complicated, so maybe a visual would help.

AdvisorID | Academic Program ID
2             | 1
2             | 2
2             | 3
3             | 1
3             | 2
3             | 3
4             | 1
4             | 2
4             | 3

It's basically a join table, with information being pulled from two
other tables. However, I don't want duplicate information where the
Advisor ID has two of the same Academic Program ID. Again, here's a
visual, only this is an example of what I don't want.

Advisor ID | Academic Program ID
1              | 2
1              | 2

I hope this makes sense. Let me know if you have any questions still.- Hide quoted text -

- Show quoted text -

Well that certainly did the trick, although I was a little annoyed
that it didn't just remove the offending record afterwards. However, I
got around this little annoyance with the following line of code.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox "The Academic Program that you are trying to add is
already listed for this advisor, and will be removed."
Me.Undo
Response = acDataErrContinue
Case Else
MsgBox Err.Description
End Select
End Sub

I'm not the greatest programmer, so I'm not sure that's the best line
of code in the world. However, it does what I want it to do, so that's
always a plus. Nevertheless, if you can think of a better line of code
that does what my code does, I would certainly be interested to see
it. Take care, and thanks again.