Okay, so you have 2 unique indexes, and you are asking how to tell which one
triggered the form's Error event.
This example shows how to use DLookup() to see if a field has the duplicate:
Dim strWhere As String
Dim strMsg As String
Dim varResult As Variant
With Me.[YourNumberField]
If (.Value = .OldValue) OR (IsNull(.Value)) Then
'do nothing
Else
strWhere = .Name & " = " & .Value
varResult = DLookup(.Name, "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg = strMsg & "Duplicate in " & .Name & vbCrLf
End If
End If
End With
If strMsg <vbNullString Then
MsgBox strMsg
End If
Note that if it is a Text field, you need extra quotes as delimiters:
strWhere = .Name & " = """ & .Value & """"
or if it is a Date field, use # as the delimiter.
Also, the code is designed so you can repeat the With block for the other
field.
HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"zyzolus" <zy*****@o2.plwrote in message
news:92**********************************@f40g2000 pri.googlegroups.com...
>
Thank you for prompt reply.
>Not sure I understand.
If you want both field to be indepently unique, you would need 2 indexes
(one on each field.)
That is correct. I create 2 indexes (one on each field)
************************************************** ***********
>- A has no duplicate, and neither does B
This is the combination I am interested in.
************************************************** ***********
>Perhaps you could use the Error event of the form. If the duplicate error
occurs, examine each index in turn, using a DLookup() to see which
field(s)
have the duplicate(s.)
What is the way to distinguish 2 (or more) fields with unique indexes
with DLookup() function in Error event and as a result display 2 (or
more) different error messages?