DMax Not working correctly

[gold007eye]

I'm not sure what I am doing wrong, but this is the only part I am stuck on.
I am trying to use the DMax function to increment the "Extension" by +1. I am trying to do it using an IF THEN statement.

What I have it doing is when you click on the "Add Reassignment" button it adds a new record and copies the previous record information "BaseID", "Extension", & "SSNEIN" to the new record. I then have the code to look and see if the "Extension" field is NULL and if so it Then the "Extension" field = the "Starting Extension" value "01" from the Main Form.

Where I am running into my problem is the 2nd portion of the statement that needs to update the Extension field to the next number if the extension field is NOT NULL.

Here is the code I am using.

This Code throws no errors, but gives the results you see in the attached picture:

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")
            
            If IsNull([Extension]) Then
            Me![Extension] = Format([Forms]![New Provider]![Starting Extension], "00")
            Else:
            Me![Extension] = Format(Nz(DMax("[Extension]", "[Provider Number Information]"), 0) + 1)
            End If
            
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            
        End With

    End If
    
End Sub

If I use this code it throws an error saying "Compile Error: Argument Not Optional" and highlights "DMax":

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")
            
            If IsNull([Extension]) Then
            Me![Extension] = Format([Forms]![New Provider]![Starting Extension], "00")
            Else:
            Me![Extension] = Format(Nz(DMax([Extension]), 0) + 1)
            End If
            
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            
        End With

    End If
    
End Sub

What am I doing wrong?

 

[pbaldy]

Did you look in VBA Help at DMax? You'd see that at least 2 arguments are required, field and domain. Only criteria is optional.

 

[gold007eye]

I did see that, but how do I have it look at the previous records "extension" value in the subform table and then add +1 to that value as long as it isn't Null?

I'm just not sure how to do this part.

Here is DMax code I usually use. (To answer the question about looking at the VBA help)

If IsNull([Provider Number Information Subform].Form![BaseID]) Then
[Provider Number Information Subform].Form![BaseID] = Format(Nz(DMax("[BaseID]", "[Provider Number Information]"), 0) + 1)
EndIf

 

[Epic]

not sure that Dmax works on recordsets but will work on tables and q'ry.if you have to loop between the records then doo it however is not guaranteed that your previous and next record will be that what you are expecting... hou should tell to the next next record what is the current when imputing the data, anyway help says that:

domain - A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.