Here's another attempt.
In your Database29 in #14 try the following behind your SAVE button.
Code:
' ----------------------------------------------------------------
' 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:
Code:
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.