Custom message when duplicate values in index ?

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Table X has columns Group, B, and C

With a group, neither B nor C should contain duplicates

Index1 is Group/B unique
Index2 is Group/C unique

G_one, 1, 1 ok
G_one, 1, 2 <- Index1 unique constraint violated
G_one, 2, 1 <- Index2 unique constraint violated
G_one, 3, 4 ok
G_one, 3, 4 <- both unique constraints violated

When the constraint is violated the dialog
"
The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship.
Change the data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit duplicate entries
and try again.
"
appears.

Data entry is done through a form.

How would I trap the error condition and identify which index
constraint was being violated.

Want to display messages like
"Can't use " & B & " because it has been used before"
"Can't use " & C & " because it has been used before"
"Can't use " & B & " or " & C & " because each has been used before"
 
Table X has columns Group, B, and C

With a group, neither B nor C should contain duplicates

Index1 is Group/B unique
Index2 is Group/C unique

G_one, 1, 1 ok
G_one, 1, 2 <- Index1 unique constraint violated
G_one, 2, 1 <- Index2 unique constraint violated
G_one, 3, 4 ok
G_one, 3, 4 <- both unique constraints violated

When the constraint is violated the dialog
"
The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship.
Change the data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit duplicate entries
and try again.
"
appears.

Data entry is done through a form.

How would I trap the error condition and identify which index
constraint was being violated.

Want to display messages like
"Can't use " & B & " because it has been used before"
"Can't use " & C & " because it has been used before"
"Can't use " & B & " or " & C & " because each has been used before"
Click to expand...

Hey Richard -

I wish I had a simple answer but your table design is not right so we
can't put a band-aid on the data entry form to prevent duplicates.

I guess it is a many-to-many relationship between groups and members,
meaning members can be part of many groups and groups can have many
members.

See, in your table X you should only have whatever info that relates
to that table - i.e. table X lists people and has the fields:

PeopleID - Autonumber - primary key
Fname text
Lname - text
Title - text
email - text

Now you need a group table:

GroupsD - Autonumber - primary key
GroupName - text - group a, group b, etc.
GroupInfo - text - group description

Now you need a MemberGroup table:

LineID - Autonumber
GroupID - lookup field (number) to the group table GroupID
MemberID - lookup field (number) to table x PeopleID
Role - text - members role on the group


Start there by revamping your tables then you can move forward to data
entry through forms.


Diane
 
On Feb 19, 3:02 am, (e-mail address removed) wrote:

...
See, in your table X you should only have whatever info that relates
to that table - i.e. table X lists people and has the fields:

PeopleID - Autonumber - primary key
Fname text
Lname - text
Title - text
email - text

Now you need a group table:

GroupsD - Autonumber - primary key
GroupName - text - group a, group b, etc.
GroupInfo - text - group description

Now you need a MemberGroup table:

LineID - Autonumber
GroupID - lookup field (number) to the group table GroupID
MemberID - lookup field (number) to table x PeopleID
Role - text - members role on the group

Start there by revamping your tables then you can move forward to data
entry through forms.
Click to expand...

Hi Diane:

Your suggested schema is what I have, except that MemberGroup is a
MemberPairGroup. Each LineID has a _pair_ of corresponding memberIds.
LineID - Autonumber
GroupID - lookup field (number) to the group table GroupID
Click to expand...

MemberID_1 - lookup field (number) to table x PeopleID
MemberID_2 - lookup field (number) to table x PeopleID
Role - text - members role on the group
Click to expand...

The form would be used to ensure MemberID_1 <> MemberID_2.
The unique keys [GroupID.MemberID_1] and [GroupID.MemberID_2] would
ensure no duplicates (reuse of a member within a group)

Thus, when one of the two unique constraints is violated, that is when
I would want the forms error handler to 'figure out' which constraint
was violated and popup a reasonable message instead of the multi-
purpose/generic one that Access does.
 
Data entry is done through a form.

How would I trap the error condition and identify which index
constraint was being violated.

Want to display messages like
"Can't use " & B & " because it has been used before"
"Can't use " & C & " because it has been used before"
"Can't use " & B & " or " & C & " because each has been used before"
Click to expand...

Use the Form's BeforeUpdate event for this. You can use either DLookUp
or (more efficiently but more code to write) create a Recordset to see
if a record already exists which would violate the constraint.
Something like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim FoundB As Boolean
Dim FoundC As Boolean
strSQL = "SELECT Group, B, C FROM X WHERE X.Group = '" & Me!txtGroup _
& "' AND X.B = " & Me!txtB
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
FoundB = True
Cancel = True
End If
<similar code for C>
<post your message using MsgBox>

John W. Vinson [MVP]
 
Back
Top