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

Use a subquery --   (SELECT Max([Client Information].[ClientID])+1, FROM [Client Information] AS [XX]) AS [ClientID]

Answer 3

ciao Jesse,

try this :

Me.yourFormProgressiveControl=Nz(DMax("val(ClientID)", "[Client Information]"), 0) + 1

ciao, Sandro.

Answer 4

Bear in mind that if you assign a value to the ClientID control in the form this will initiate a new record, so if the user decides to abort the insertion of the record they will have to Undo the form, rather than merely closing the form or moving to another existing record.

Another issue is that there is the possibility of conflicts if one or more other users are attempting to insert a new record prior to any of them saving the record.  By calling the DMax function all will get the same number, and consequently a key violation error will occur for all bar the first user to save the record.

Both of these issues are addressed in CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the form for the sequential numbering option calls a function in the form's Current event procedure to set the DefaultValue property of the ProductID control.  This does not initiate a new record, so the user can simply close the form or move off the new record if they decide not to go ahead.

To cater for possible conflicts, code in the form's Error event procedure calls a function to increment the number in the event of a key violation.

An additional feature of this demo is that it allows the next number to be used to be 'seeded' rather than automatically using the next number in sequence.

Answer 5

First, where are you putting this expression? It looks like you are putting it directly in the Before Update event which is probably why you are generating the error. 

If you want to use The DMax method, please review my blog article on Sequential Numbering. It details the correct syntax for the DMax and suggests where to put it. 

Finally, there is no reason not to use an autonumber in your case. The autonumber works behind the scenes. If you still need to use a sequential number for new clients as a visible identifier you can still do so.