If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
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? |
#2
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
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/...UBED1=ACCESS-L "DoveArrow" wrote in message ... 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? |
#3
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
On May 1, 11:02*am, "Roger Carlson"
wrote: 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/...UBED1=ACCESS-L "DoveArrow" wrote in message ... 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?- Hide quoted text - - 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. |
#4
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
On May 1, 11:02*am, "Roger Carlson"
wrote: 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/...UBED1=ACCESS-L "DoveArrow" wrote in message ... 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?- Hide quoted text - - 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. |
#5
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
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. "DoveArrow" wrote in message ... On May 1, 11:02 am, "Roger Carlson" wrote: 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/...UBED1=ACCESS-L "DoveArrow" wrote in message ... 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?- Hide quoted text - - 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. |
#6
|
|||
|
|||
Preventing Duplicate Entries on a Continuous Subform
On May 2, 4:32*am, "BruceM" wrote:
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. "DoveArrow" wrote in message ... On May 1, 11:02 am, "Roger Carlson" wrote: 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/...UBED1=ACCESS-L "DoveArrow" wrote in message ... 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?- Hide quoted text - - 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.- 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. |
Thread Tools | |
Display Modes | |
|
|