Error message - unique index created on two different fields

[zyzolus]

Hello,

I create 2 unique indexes on 2 fields: Field1 and Field2.

What is the way generate custom error message independent of each
field?ex.:
"Duplicate value in Field1" or "Duplicate value in Field2".

If "OnError" event is used (error code 3022 in this case) the same
error
message will be displayed for both fields.

Thank you for response.

OS:win xp
MS Access 2003

[Allen Browne]

Not sure I understand.

If you want both field to be indepently unique, you would need 2 indexes
(one on each field.)

For a multi-field index, there's a huge number of possible combinations to
examine, as it's not limited to 2 fields. Even for just 2 fields, the cases
could be:
- A has no duplicate, and neither does B
- A has a duplicate and B does not
- B has a duplicate, and A does not
-Both A and B have a duplicate
But that's before you consider:
- the could be more than 2 fields;
- the could be several multi-field indexes in the table;
- there could be several tables involved (if the form is based on a query.

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.)

--
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:70**********************************@1g2000pr d.googlegroups.com...

>
I create 2 unique indexes on 2 fields: Field1 and Field2.

What is the way generate custom error message independent of each
field?ex.:
"Duplicate value in Field1" or "Duplicate value in Field2".

If "OnError" event is used (error code 3022 in this case) the same
error
message will be displayed for both fields.

Thank you for response.

OS:win xp
MS Access 2003

[zyzolus]

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?

[Allen Browne]

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?

[zyzolus]

Thank you for help.
Works OK.

[zyzolus]

Hello,
The hints you gave work perfectly in case indexes are created with
separete fields.

What is the way to modify the code you wrote in your message so it can
be used with an index created with multiple fields?
Assume, indexes(unique) are created with the following combination of
fields:

Index_name1 Field1
Field2
Index_name2 Field3
Field4

So, if a combination of above fields is repeated a custom error
message is displayed (separete for "Index_name1" and "Index_name2").

Thank you.

[Allen Browne]

Put 2 phrases into the Criteria, e.g.:
strWhere = ("[MyNumberField] = " & Me.MyNumberField & ") AND
([MyTextField] = """ & Me.MyTextField & """)"

--
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:4b**********************************@l33g2000 pri.googlegroups.com...

Hello,
The hints you gave work perfectly in case indexes are created with
separete fields.

What is the way to modify the code you wrote in your message so it can
be used with an index created with multiple fields?
Assume, indexes(unique) are created with the following combination of
fields:

Index_name1 Field1
Field2
Index_name2 Field3
Field4

So, if a combination of above fields is repeated a custom error
message is displayed (separete for "Index_name1" and "Index_name2").

Thank you.

[zyzolus]

Put 2 phrases into the Criteria, e.g.:

* * strWhere = ("[MyNumberField] = " & Me.MyNumberField & ") AND
([MyTextField] = """ & Me.MyTextField & """)"

I appreciate your help.
The criteria look pretty interesting.
However, the suggested earlier code:

varResult = DLookup(.Name, "Table1", strWhere)

doesn't allow to use "DLookup" function with multiple fields ie:
wrong: varResult = DLookup(MyNumberField AND MyTextField, "Table1",
strWhere)

How can the above code be modified so it can be used in the code
proposed in the post written on Nov 9th?
Or should it be written in a different way?

[Allen Browne]

If it's a duplicate, you already know the 2 values (they're in the form), so
the actual field you ask DLookup() to find doesn't matter.

If you really want you can ask DLookup() to return 2 fields as a
concatenated string, like this:
DLookup("[Surname] & "", "" & [FirstName]", "tblCustomer")

--
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:69**********************************@n33g2000 pri.googlegroups.com...

Put 2 phrases into the Criteria, e.g.:
strWhere = ("[MyNumberField] = " & Me.MyNumberField & ") AND
([MyTextField] = """ & Me.MyTextField & """)"

I appreciate your help.
The criteria look pretty interesting.
However, the suggested earlier code:

varResult = DLookup(.Name, "Table1", strWhere)

doesn't allow to use "DLookup" function with multiple fields ie:
wrong: varResult = DLookup(MyNumberField AND MyTextField, "Table1",
strWhere)

How can the above code be modified so it can be used in the code
proposed in the post written on Nov 9th?
Or should it be written in a different way?