Adding 1 to a max. Nz() with DMax()+1

Question

I am trying something simple and I'm being crushed under the weight of my own ineptitude...

I want to add a new record with a command button, but the table ID is not an auto number (don't ask, long story...), so I need to find the max id and add one to it to add a new record. I'm using this expression:

=Nz([txtClientID],DMax([Client Information]![ClientID],[Client Information]+1))

But I get this error:

Detailed Object Information:

Table: Client Information

Form: frmReferral_Info

Field to add one to: [Client Information].[ClientID]

Answer 1

The ClientID field already has data in it and cannot be switched by back to autonumbering. (Unless you know a fix for that?)

It's not difficult.  Firstly add an autonumber column, ClientID_New say to the table, and a corresponding long integer ClientID_New column to any referencing tables which include a ClientID foreign key column.   Then it's merely a case of updating the foreign key columns in the referencing tables with 'update' queries such as below for an Orders table for instance:

UPDATE Orders INNER JOIN Clients

ON Orders.ClientID = Clients.ClientID

SET Orders.ClientID_New = Clients.ClientID_New;

If, in any of the referencing tables, the ClientID foreign key column is part of a composite primary key which is referenced by a corresponding composite foreign key in one or more other tables, you would need to do the same for those tables, joining the table with the composite foreign key to that with the composite primary key on both columns and updating a ClientID_New column in the table with the composite foreign key in the same way as above.  In a complex model this process might have to be repeated down the line until the lowest level referencing table has been updated in this way.

Once the ClientID_New columns in all of the relevant tables have been updated the original ClientID columns can be deleted from the table definitions, and the ClientID_New columns renamed ClientID.  The relationships on ClientID (whether as the complete key or as a component of a composite key)  can then be recreated and enforced.

Answer 2

I am trying something simple and I'm being crushed under the weight of my own ineptitude...

I want to add a new record with a command button, but the table ID is not an auto number (don't ask, long story...), so I need to find the max id and add one to it to add a new record. I'm using this expression:

=Nz([txtClientID],DMax([Client Information]![ClientID],[Client Information]+1))

But I get this error:

Detailed Object Information:

Table: Client Information

Form: frmReferral_Info

Field to add one to: [Client Information].[ClientID]

I see a couple of problems with what you have here.

1. The BeforeUpdate event occurs every time you update the record.  So whether you're adding a new record or editing an existing one, your procedure would attempt to update the record id.  Because you aren't showing your entire procedure, you may require additional coding to limit updating the id field to new records only.
2. You are supplying values to DMax rather than the item names it calls for as arguments.  Your call to DMax should look more like the following:

varID = Nz(DMax("ClientID", "[Client Information]"), 0) + 1

Example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    GetNextID

End Sub

Private Sub GetNextID()

    On Error GoTo Err_Process

    Dim varID As Variant

    Dim strMsg As String

    If (Me.NewRecord) Then

        varID = Nz(DMax("ClientID", "[Client Information]"), 0) + 1

        Me.ClientID = varID

    End If

Exit_Process:

    Exit Sub

Err_Process:

    strMsg = "Error: " & Err.Number & vbCrLf & _

    "Procedure: GetNextID" & vbCrLf & vbCrLf & _

    Err.Description

    MsgBox strMsg, vbExclamation, "Error"

    Resume Exit_Process

End Sub

Answer 3

The ClientID field already has data in it and cannot be switched by back to autonumbering. (Unless you know a fix for that?) I will check out the info on your blog. Thanks for the response.