Forum Discussion

ns61's avatar
ns61
Copper Contributor
Mar 07, 2025
Solved

Trouble identifying 'Primary' phone number

Hi - I'm new...ish to Access. I've used it to cleanup raw data before importing into main SQL db, but have not really 'developed' in it. My SQL is rusty and I've only dabbled with VBA. I'm building ...
access
  • arnel_gp's avatar
    arnel_gp
    Mar 08, 2025

    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

     

arnel_gp's avatar
arnel_gp
Iron Contributor
Mar 11, 2025

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.

Resources