Trouble identifying 'Primary' phone number

If you are Updating same record that you are also updating on the Form, please do not use SQL update query. Use the Form's recordset.

Here is the explanation from CoPilot:

When a MS Access form is "dirty" (meaning changes are pending) and you try to update the same table using an SQL UPDATE query, you'll likely encounter a Write conflict error because Access is trying to manage the same data simultaneously. 

Here's how to resolve this: 

Save the Form Before Updating:
Before running the SQL UPDATE query, ensure the form's changes are saved to the database by either:
Moving to a different record in the form, which triggers the automatic save. 
Explicitly saving the record using VBA code: Me.Dirty = False. 
Using the Me.Requery command to refresh the form after the update. 
This ensures that the form's changes are synchronized with the database before the update query attempts to modify the same data. 

Consider a Timestamp Field:
Add a timestamp field to the table that is editable through Access. 
This field will automatically update whenever a record is modified, helping Access to detect conflicts and prevent errors. 
You can also add a timestamp column to the SQL database that gets updated with a trigger ON UPDATE. 

Check Row Versioning (if applicable):
If you're connecting to a SQL Server database, investigate the use of row versioning (or a similar mechanism).
Row versioning can help track changes and resolve conflicts more effectively. 

Avoid "Dirty" Form Updates:
If you are making changes to the record in the form and also using VBA code to update the record, make sure you are not trying to update the same record at the same time. 
If you are updating the record in the form, make sure the form is not dirty before you try to update the record in the table. 
You can also use VBA to change the values ON the form rather than in a separate query or alter the process so that the records you are viewing are a snapshot stored in a temporary table, that you base your update query on that.

I discourage using SQL query when you are in a Form and the form is Dirty.

you can use the Form's Recordsetclone.

Dim m_PhoneID As Long


Private Sub YesNoField_BeforeUpdate(Cancel As Integer)

    If Me.YesNoField = -1 Then
        With Me.RecordsetClone
            .FindFirst "YesNoField = -1 And CustomerID = " & Me.CustomerID & " And PhoneID <> " & Me.PhoneID
            If Not .NoMatch Then
                If MsgBox("Do you want to make this PhoneNumber the primary phone number?", vbQuestion + vbYesNo) = vbYes Then
                    m_PhoneID = !PhoneID
                    Me.TimerInterval = 100
                Else
                    Me.YesNoField.Undo
                    Cancel = True
                End If
            End If
        End With
    End If
    
End Sub



Private Sub Form_Timer()

    ' kill the timer
    Me.TimerInterval = 0
    With Me.RecordsetClone
        .FindFirst "PhoneID = " & m_PhoneID
        .Edit
        !YesNoField = 0
        .Update
    End With

End Sub

You're not showing the design of the table, so I can freely speculate you have a CustomerID field as well, and are capturing the several phone numbers for each customer.

If so, in Form_AfterUpdate you can count the number of records with Primary field set to True, using DCount, and if greater than 1, run an Update query to set all but the current record to False. Something like:

(off the cuff)
dim sql as string

if me.Primary=True then

if dcount("*", "tblPhoneNumbers", "CustomerID=" & Me.CustomerID) > 1 then

sql="update tblPhoneNumbers set Primary=false where CustomerID=" & me.CustomerID & " and PhoneID <> " & Me.PhoneID
Currentdb.Execute sql, dbFailOnError

endif

endif