Unique Data

[zyus]

Assuming i hv 2 fields

Field1 Field2
A 123
B 123

I want [field1]&[field2] to be unique (no duplicate) when combined together.
Can it be triggerred during data entry thru form....

TQ

 

[Allen Browne]

Create a unique index on the combination of the 2 fields.

A simple way to do that is to make them the primary key. Open your table in
design view, select the 2 fields (click the "record selector" to the left of
the first field name, and drag down to select the 2nd row as well), and then
click the Key icon.

 

[George]

Dear zyus,

Set both of the fileds as Primary Keys. This means that the same
combination cannot be accepted twice, while any other combinations are
accepted, e.g.
A 123
B 123
A 122
B 122
All should be accepted.

Hope I helped.

GeorgeCY

Ο χÏήστης "zyus" έγγÏαψε:

 

[gllincoln]

Hi TQ,

Yes - but it's a little messy.

It would be easier for those of us playing along at home, if we knew all of
the rules. Assuming the data is in a local table (because we generally don't
want to use DCount if we can avoid it on a linked table).

We can't do much with this until we get both pieces of the puzzle, since A
or B may be true without violating our multi-field unique rule.

That data looks like numbers but we have to know for sure, and whether you
mean 'when combined together' as in 123+123 = 246 or 123 + 123 = 123123?

If we are adding or concatening these together, then for an entry in A or B
form control (text boxes probably) in the after update event, we want to
first test to see if our sibling text box contains data.

If we have valid data in our sibling, then we set a variable to either A+B
or A&B as appropriate and run a query, checking for an already existing
combined value, responding appropriately. The code below is just off the
cuff, hot air code - no warranties expressed or implied. Particularly right
now - since I've been working with VBScript and .NET for the past couple
three weeks. They say syntax is the second thing to go... I forget what the
first one was...

Hope this helps,
Gordon

'assumes string concatenation

Private Sub txtFieldA_AfterUpdate()
If Len(Nz(Me!txtFieldB)) = 0 Then
Exit Sub
Else
Dim s As String
Dim rs As DAO.Recordset
Dim strSQL As String
s = Me!txtFieldA & Me!txtFieldB
strSQL = "SELECT FieldA & FieldB As myValue FROM " & _
"myTable WHERE myValue='" & s & "';"
If rs.RecordCount Then
'you might want to only empty out FieldB?
'Or you might want to jump to dupe record and view that instead
'of clearing the fields?
Me!txtFieldA = ""
Me!txtFieldB = ""
MsgBox "Unique Value of FieldA & FieldB: " & s & " already
exists, cannot add this as a new record.", vbCritical, "Duplicate Key Value"
Else
Dim myBook As Bookmark
myBook = Me.Bookmark
Me.Requery
Me.Bookmark = myBook
End If
End If
Set rs = Nothing
End Sub

 

[John W. Vinson]

Yes - but it's a little messy.

Ummm...

You're rejecting the simple, effective, and non-messy two-field unique index
suggested elsethread?