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
|
|||
|
|||
Prevent Dups in One-To-Many Relationship
I have a 3-tier One-To-Many table structure.
tblOrganization: OrganizationID (Autonumber) Organization (Text) tblBranch: BranchID (Autonumber) OrganizationIDfk (Number) Branch (Text) tblSubBranch: SubBranchID (Autonumber) BranchIDfk (Number) Subbranch(Text) Currently, all Autonumber are also set as the primary key. - The data entry is done via the mainform (frmOrganization) - which then has a subform (sfrmBranch) - which then has also a subform (sfrmSubBranch). The data of the subforms is being displayed in "datasheet" view. Here, now is the problem I'm trying to overcome: - Currently, I could add the same Branch multiple times under the same Organization (same record). I don't want this! (It would be okay though to repeat the same sub entity on another organization record). Here's what I have tried (but doesn't work for me): - In tblBranch, used a composite key of of 3 fields (BranchID, OrganizationIDfk, and Branch). - Now, however, I cannot create the relationship in the ERD view. An dialog box pops up that indicates that there is no unique index. Is there a way to keep to table structure which, howewever, DOES NOT allow to - enter multiple duplicate entries of "Branch" under "Organization" - enter multiple duplicate entries of "Subbranch" under "Branch" Thanks, Tom |
#2
|
|||
|
|||
Prevent Dups in One-To-Many Relationship
Leave the autonumbers as primary key, but make a compound UNIQUE INDEX of
BranchID and OrganizationIDfk in tblBranch and a compound unique index of SubBrangID and BranchIDfk in tblSubBranch. You can make compound unique indexes by opening the table in design view and clicking the Indexes button on the tool bar. Create an index and give it an IndexName and add a field make it Unique. Then on the next line add another field WITHOUT adding an index name. This will add the field to the index just above it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... I have a 3-tier One-To-Many table structure. tblOrganization: OrganizationID (Autonumber) Organization (Text) tblBranch: BranchID (Autonumber) OrganizationIDfk (Number) Branch (Text) tblSubBranch: SubBranchID (Autonumber) BranchIDfk (Number) Subbranch(Text) Currently, all Autonumber are also set as the primary key. - The data entry is done via the mainform (frmOrganization) - which then has a subform (sfrmBranch) - which then has also a subform (sfrmSubBranch). The data of the subforms is being displayed in "datasheet" view. Here, now is the problem I'm trying to overcome: - Currently, I could add the same Branch multiple times under the same Organization (same record). I don't want this! (It would be okay though to repeat the same sub entity on another organization record). Here's what I have tried (but doesn't work for me): - In tblBranch, used a composite key of of 3 fields (BranchID, OrganizationIDfk, and Branch). - Now, however, I cannot create the relationship in the ERD view. An dialog box pops up that indicates that there is no unique index. Is there a way to keep to table structure which, howewever, DOES NOT allow to - enter multiple duplicate entries of "Branch" under "Organization" - enter multiple duplicate entries of "Subbranch" under "Branch" Thanks, Tom |
#3
|
|||
|
|||
Prevent Dups in One-To-Many Relationship
Roger:
Fantastic, this works great!!! Thank you so much for sharing this w/ me. I do have 3 follow up questions though... 1. You indicated that "on the next line add another field WITHOUT adding an index name.." Why is that? Is this a requiremenment? (I followed your instructions, but I'm just trying to get a better understanding for future reference). 2. When testing this, I tried to add a duplicate value. With the index, I cannot do this now... which is great. However, to "get rid off" the error message I MUST select either a different value or simply backspace all keystrokes (which then creates a blank record). At this time though, I am concerned that some users would not necessarily then delete that blank line. So, my questions is: How can I make sure that I acknowledge the "error message" but the attempt to add the record is simply cancelled. 3. Is there a way to call a more customized "error message". Instead of this very long default message, I'd rather want to display "This duplicate value cannot be added!". Thanks so much in advance again, Tom "Roger Carlson" wrote in message ... Leave the autonumbers as primary key, but make a compound UNIQUE INDEX of BranchID and OrganizationIDfk in tblBranch and a compound unique index of SubBrangID and BranchIDfk in tblSubBranch. You can make compound unique indexes by opening the table in design view and clicking the Indexes button on the tool bar. Create an index and give it an IndexName and add a field make it Unique. Then on the next line add another field WITHOUT adding an index name. This will add the field to the index just above it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... I have a 3-tier One-To-Many table structure. tblOrganization: OrganizationID (Autonumber) Organization (Text) tblBranch: BranchID (Autonumber) OrganizationIDfk (Number) Branch (Text) tblSubBranch: SubBranchID (Autonumber) BranchIDfk (Number) Subbranch(Text) Currently, all Autonumber are also set as the primary key. - The data entry is done via the mainform (frmOrganization) - which then has a subform (sfrmBranch) - which then has also a subform (sfrmSubBranch). The data of the subforms is being displayed in "datasheet" view. Here, now is the problem I'm trying to overcome: - Currently, I could add the same Branch multiple times under the same Organization (same record). I don't want this! (It would be okay though to repeat the same sub entity on another organization record). Here's what I have tried (but doesn't work for me): - In tblBranch, used a composite key of of 3 fields (BranchID, OrganizationIDfk, and Branch). - Now, however, I cannot create the relationship in the ERD view. An dialog box pops up that indicates that there is no unique index. Is there a way to keep to table structure which, howewever, DOES NOT allow to - enter multiple duplicate entries of "Branch" under "Organization" - enter multiple duplicate entries of "Subbranch" under "Branch" Thanks, Tom |
#4
|
|||
|
|||
Prevent Dups in One-To-Many Relationship
1) By adding the additional fields below the first without an index name in
the Index Name column, tells Access that this is a multiple-field index. If you added an index name, it would be a separate single-field index. If you tried putting the same index name in the Index name column, you would get an error message that you have duplicate index names. 2) Well no, to get rid of the record, you hit the ESC key and that will cancel the record. To automate this, see #3 3) You can trap this system error message at the form level. In the OnError Event of the subform, add the following: Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3022 Then MsgBox "This duplicate value cannot be added!" & vbCrLf & "Push ESC key to cancel" Response = acDataErrContinue End If End Sub This will add your custom error message and instruct the user to push ESC to cancel the record. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... Roger: Fantastic, this works great!!! Thank you so much for sharing this w/ me. I do have 3 follow up questions though... 1. You indicated that "on the next line add another field WITHOUT adding an index name.." Why is that? Is this a requiremenment? (I followed your instructions, but I'm just trying to get a better understanding for future reference). 2. When testing this, I tried to add a duplicate value. With the index, I cannot do this now... which is great. However, to "get rid off" the error message I MUST select either a different value or simply backspace all keystrokes (which then creates a blank record). At this time though, I am concerned that some users would not necessarily then delete that blank line. So, my questions is: How can I make sure that I acknowledge the "error message" but the attempt to add the record is simply cancelled. 3. Is there a way to call a more customized "error message". Instead of this very long default message, I'd rather want to display "This duplicate value cannot be added!". Thanks so much in advance again, Tom "Roger Carlson" wrote in message ... Leave the autonumbers as primary key, but make a compound UNIQUE INDEX of BranchID and OrganizationIDfk in tblBranch and a compound unique index of SubBrangID and BranchIDfk in tblSubBranch. You can make compound unique indexes by opening the table in design view and clicking the Indexes button on the tool bar. Create an index and give it an IndexName and add a field make it Unique. Then on the next line add another field WITHOUT adding an index name. This will add the field to the index just above it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... I have a 3-tier One-To-Many table structure. tblOrganization: OrganizationID (Autonumber) Organization (Text) tblBranch: BranchID (Autonumber) OrganizationIDfk (Number) Branch (Text) tblSubBranch: SubBranchID (Autonumber) BranchIDfk (Number) Subbranch(Text) Currently, all Autonumber are also set as the primary key. - The data entry is done via the mainform (frmOrganization) - which then has a subform (sfrmBranch) - which then has also a subform (sfrmSubBranch). The data of the subforms is being displayed in "datasheet" view. Here, now is the problem I'm trying to overcome: - Currently, I could add the same Branch multiple times under the same Organization (same record). I don't want this! (It would be okay though to repeat the same sub entity on another organization record). Here's what I have tried (but doesn't work for me): - In tblBranch, used a composite key of of 3 fields (BranchID, OrganizationIDfk, and Branch). - Now, however, I cannot create the relationship in the ERD view. An dialog box pops up that indicates that there is no unique index. Is there a way to keep to table structure which, howewever, DOES NOT allow to - enter multiple duplicate entries of "Branch" under "Organization" - enter multiple duplicate entries of "Subbranch" under "Branch" Thanks, Tom |
#5
|
|||
|
|||
Prevent Dups in One-To-Many Relationship
Thanks, Roger. This makes sense now.
-- Thanks, Tom "Roger Carlson" wrote in message ... 1) By adding the additional fields below the first without an index name in the Index Name column, tells Access that this is a multiple-field index. If you added an index name, it would be a separate single-field index. If you tried putting the same index name in the Index name column, you would get an error message that you have duplicate index names. 2) Well no, to get rid of the record, you hit the ESC key and that will cancel the record. To automate this, see #3 3) You can trap this system error message at the form level. In the OnError Event of the subform, add the following: Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3022 Then MsgBox "This duplicate value cannot be added!" & vbCrLf & "Push ESC key to cancel" Response = acDataErrContinue End If End Sub This will add your custom error message and instruct the user to push ESC to cancel the record. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... Roger: Fantastic, this works great!!! Thank you so much for sharing this w/ me. I do have 3 follow up questions though... 1. You indicated that "on the next line add another field WITHOUT adding an index name.." Why is that? Is this a requiremenment? (I followed your instructions, but I'm just trying to get a better understanding for future reference). 2. When testing this, I tried to add a duplicate value. With the index, I cannot do this now... which is great. However, to "get rid off" the error message I MUST select either a different value or simply backspace all keystrokes (which then creates a blank record). At this time though, I am concerned that some users would not necessarily then delete that blank line. So, my questions is: How can I make sure that I acknowledge the "error message" but the attempt to add the record is simply cancelled. 3. Is there a way to call a more customized "error message". Instead of this very long default message, I'd rather want to display "This duplicate value cannot be added!". Thanks so much in advance again, Tom "Roger Carlson" wrote in message ... Leave the autonumbers as primary key, but make a compound UNIQUE INDEX of BranchID and OrganizationIDfk in tblBranch and a compound unique index of SubBrangID and BranchIDfk in tblSubBranch. You can make compound unique indexes by opening the table in design view and clicking the Indexes button on the tool bar. Create an index and give it an IndexName and add a field make it Unique. Then on the next line add another field WITHOUT adding an index name. This will add the field to the index just above it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Tom" wrote in message ... I have a 3-tier One-To-Many table structure. tblOrganization: OrganizationID (Autonumber) Organization (Text) tblBranch: BranchID (Autonumber) OrganizationIDfk (Number) Branch (Text) tblSubBranch: SubBranchID (Autonumber) BranchIDfk (Number) Subbranch(Text) Currently, all Autonumber are also set as the primary key. - The data entry is done via the mainform (frmOrganization) - which then has a subform (sfrmBranch) - which then has also a subform (sfrmSubBranch). The data of the subforms is being displayed in "datasheet" view. Here, now is the problem I'm trying to overcome: - Currently, I could add the same Branch multiple times under the same Organization (same record). I don't want this! (It would be okay though to repeat the same sub entity on another organization record). Here's what I have tried (but doesn't work for me): - In tblBranch, used a composite key of of 3 fields (BranchID, OrganizationIDfk, and Branch). - Now, however, I cannot create the relationship in the ERD view. An dialog box pops up that indicates that there is no unique index. Is there a way to keep to table structure which, howewever, DOES NOT allow to - enter multiple duplicate entries of "Branch" under "Organization" - enter multiple duplicate entries of "Subbranch" under "Branch" Thanks, Tom |
Thread Tools | |
Display Modes | |
|
|