Reset DMax count for each new record or SSN

[gold007eye]

Is it possible to reset the DMax function on a particular field (Extension) for each new record. I have been racking my brain all day trying to get this to work to no avail.

I know it is possible to have a numbering sequence reset each new year, but I need it to reset on each new record that is created; more specifically by each new SSN.

Example (Initial Record):

SSN: 123-45-6789
Provider Number = BaseID & Extension: 0000001

Example (Next Record Same SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000101

Example (Next Record Same SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000102
======================================

Example (New Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 0000002

Example (Next Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000201

Example (Next Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000202
======================================

The problem I am having is getting the extension to start back at 01 right now when I use DMax and create a new record with a different SSN it comes out like this:

Example (New Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 0000003

Example (Next Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000303

Example (Next Record Different SSN):
SSN: 123-45-6789
Provider Number = BaseID & Extension: 000000304

Please help as I am pulling my hair out trying to get this to work and this is the 1 piece of code holding me back from making progress
I have attached a picture to give you an idea of what I am talking about.

 

[gold007eye]

HOLY CRAP! I finally figured it out!!! After hours and hours of trying different code out. I finally got it to do what I wanted. Here is the code for anyone in the future. It is crude, but it might help. If anyone has a better way to do it please let me know.

Private Sub Add_Reassignment_Click()
DoCmd.GoToRecord , , acNewRec

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    
    If rs.EOF Or Not Me.NewRecord Then
        ' don't do anything if there's no records or it is not a new record
    Else
        With rs
            
            .MoveLast
                       
            Me![BaseID] = .Fields("BaseID")
            Me![SSNEIN] = .Fields("SSNEIN")
            Me![Extension] = .Fields("Extension")
                        
            If IsNull([Extension]) Then
            Me![Extension] = [Forms]![New Provider]![Starting Extension]
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
            'Forms![New Provider]![Save].SetFocus
            Else:
            Me![Extension] = Format(Nz(DMax("[Extension]", "[Provider Number Information]", "[Starting Extension]" = Forms![New Provider]![Starting Extension]), [Extension]) + 1)
            'Me![Extension] = Format(Nz(DMax("[Extension]", "[Provider Number Information]"), 0) + 1)
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
            End If
            Forms![New Provider]![Provider Number] = [Provider Number]
            Forms![New Provider]![Provider Number].Requery
            
        End With

    End If
    
End Sub