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

Tom_van_Stiphout

Iron Contributor

Mar 07, 2025

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

ns61
Copper Contributor
Mar 10, 2025
Tom_van_Stiphoutthanks for your solution, and yes, your speculations are correct. I actually was able to resolve this by
However, I'm open to seeing other approaches as well so I may play around with yours too

Forum Discussion

Trouble identifying 'Primary' phone number

Resources