Prevent Dups in One-To-Many Relationship

[Tom]

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

 

[Roger Carlson]

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.

 

[Tom]

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]

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.

 

[Tom]

Thanks, Roger. This makes sense now.