I've tried multi field indexes but am unable to prevent duplicate

[GaryC]

Hi

I have a table with fieldA and FieldB I need to prevent values in FieldA
duplicating in FieldB and vice versa, I've tried multi field indexes with no
success, is there a method to code this into an event procedure?

Thanks in advance!

 

[tina]

well, you could use a form's BeforeUpdate event to check the values entered
in the current record. something along the lines of:

If DCount(1, "TableName", "FieldB = " _
& Me!FieldA) > 0 Then
Cancel = True
Msgbox "The field A value is already in field B "
& "in the table."
ElseIf DCount(1, "TableName", "FieldA = " _
& Me!FieldB) > 0 Then
Cancel = True
Msgbox "The field B value is already in field A " _
& "in the table."
End If

hth

 

[Dirk Goldgar]

Hi

I have a table with fieldA and FieldB I need to prevent values in
FieldA duplicating in FieldB and vice versa, I've tried multi field
indexes with no success, is there a method to code this into an event
procedure?

Thanks in advance!

What exactly are you trying to prevent? Unique indexes will definitely
prevent having multiple records in the table with the same keys, But
they won't prevent you from saving a record with te same value in two
different fields of the record. Then again, it's a bit unusual to have
two fields that could even conceivably contain the same values -- that
sounds like a non-normalized data design. If you could describe the
background of what you're doing a bit, we could probably suggest a good
way to do it.

 

[GaryC]

I created tables for various subassemblies (approx ten) indexed on primary
key(no duplicates) then I have tp bring these various subassemblies to an
integration table, where there is a integration one-to-many subassembly
relationship, particular integrations have more than one of a particular
subassembly, that where I have to test that users don't accidentally enter
the same serial number twice.

Dirk and Tina hope this sheds some light on what I'm trying to achieve, your
help on this is greatly appreciated.

Kind regards

Gary