Error message - unique index created on two different fields

[jaworski_m]

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

 

[Clifford Bass]

Hi,

How about using a before insert and/or update event to pre-check for
duplicate values?

Clifford Bass

 

[jaworski_m]

Thank you for prompt reply.

Your suggestion looks interesting. Could you tell me what this "before
insert and/or update event" could look like?

 

[Clifford Bass]

Hi,

Sure. In thinking on it further, I might use the after update events
of the appropriate text boxes on on the form. So, if I have a field named
ID_1 that is the control source of a text box named txtID_1 my code might
look like this:

Private Sub txtID_1_AfterUpdate()

Dim rstTable As New ADODB.Recordset

With rstTable
.Open "select * from [tblSomeTable] where [ID_1] = " &
[txtID_1].Value, _
CurrentProject.Connection, ADODB.CursorTypeEnum.adOpenDynamic, _
ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText
If Not .BOF Or Not .EOF Then
MsgBox "The value you specified for ID #1 (" & [txtID_1].Value &
") already " & _
"exists. Please enter a different value.", vbExclamation +
vbOKOnly
[txtID_1].SetFocus
End If
.Close
End With
Set rstTable = Nothing

End Sub

Hope that helps,

Clifford Bass