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

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

Iron Contributor

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

ns61
Copper Contributor
Mar 10, 2025
arnel_gpThanks for your comment and help. I actually resolved using...
I may play around with your method as well

Forum Discussion

Trouble identifying 'Primary' phone number

Resources