Check if an index value exists

[orange]

Maybe I'm missing something basic. But in your earlier database29 -your composite index was not unique.
In the latest version the composite index is unique. So when trying to change record 3 field to to "aaa", the underlying database system detects that dddaaa already exists. It then reports the error 3022 ("Run-time error '3022': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.) as
expected.

You can capture and offer a custom error message for 3022 as CJ has described in #15

[twgonder]

@ #15

Yes, I explained that in #10, and what the problem, for me anyways, is.

[twgonder]

@#16 The first Database29, wasn't mine, it was created by CJ. I modified CJ's to be more in line with the question.
In #10 I explained the issue, maybe a bit better than I did in the OP, because I tried to be brief in the OP of what I was looking for.

[orange]

Have you found a solution? Status?
Can you adjust your validation process to include the composite value using DCount?

[orange]

Here's another attempt.

In your Database29 in #14 try the following behind your SAVE button.

' ----------------------------------------------------------------
' Procedure Name: Command9_Click
' Purpose: This is the SAVE button click event
'  This is a demo to identify whether the unique composite index would get
'  a duplicate error if the current record change or addition would result
'  in a duplication. And, if so, handle the error "gently" within the application
'  rather than allowing raw msaccess error messages to be displayed
'
' Procedure Kind: Sub
' Procedure Access: Private
' Author: Jack
' Date: 18-Jan-24
' ----------------------------------------------------------------
Private Sub Command9_Click()
    ' Me.Dirty = False
    
    Dim ResponseNo As Integer
    Debug.Print CurrentDb.TableDefs("Table1").Indexes("F12").Fields 'For testing can be removed
    Debug.Print Field1 & Field2   'For testing can be removed
   
    If DCount("*", "Table1", "Field1 & Field2 = """ & Me.Field1 & Me.Field2 & """") > 0 Then
        ResponseNo = MsgBox("The combination (" & Field1 & Field2 & ") already exists. CANCELING THIS UPDATE.", vbOKOnly)
        If ResponseNo > 0 Then Me.Undo
    Else
         Debug.Print "Accepted Unique index value : " & Me.Field1 & Me.Field2 'For Testing can be removed
         DoCmd.GoToRecord , , acNext
    End If
End Sub

The idea here is:
Identified the fields in your unique composite index,
Created a DCount to check if the values to be recorded and indexed in composite index already exists
If the value already exists, then give a meaningful message and cancel the intended change.
Ensure the form works as expected when adding records

I tried this in the Form Before update event, but although it worked partially, I still got the Access generated 3022 error. I found the above worked if I put the code behind the Save, and I did not get the 3022 Error

Also, I changed the Form_Error event code as below:

Private Sub Form_Error(DataErr As Integer, response As Integer)
    MsgBox "oops! Error: " & DataErr & "  That would cause a duplicate record. CANCELING THIS UPDATE  "
    response = 0
    Me.Undo

End Sub

Note: The user can use the Save button or move to another record. You can adjust the messages to suit your purposes.

There is a gif within the attached zip showing checking for duplicate values in the unique composite index when entering/modifying a record.