Dmax code issue - trying to find highest number and add 1

[andy_25]

I've tried this in a new test database and the same thing happens. I would be grateful if you could have a look

 

[vbaInet]

Ok, two things:

1. The code is in the wrong event.
2. When you create a new record, Contract_no has no value, hence, it's always null. This criteria doesn't even make sense to be there anyway.

Put this in the Default Value property of the textbox.

=Nz(DMax("Application_No","APPLICATIONS"), 0) + 1

 

[Galaxiom]

Why is curAppNo being dimmed as Datatype Byte? I would think that if numeric, depending on the scope needed, it would be dimmed as an Integer or Long (Integer).

These types of 'numbers' are actually usually defined as Text, since math operations are not typically done with them.

I must disagree.

Firstly these numbers are numbers and they are subjected to arithmetic operations in the DMax. A DMax on a text field will work on the alphanumeric order and get the wrong result.

If something is a number it should be stored as a number. The storage, indexing and processing of number datatypes is far more efficient than text.

The byte would be the wrong datatype here but in many cases developers are using Long or Integer when they could/should use Byte. Byte will handle positive integers up to 255 and are quite effective as PKs in Lookup tables.

Also note that if this is a Multi-users database, this type of code should be moved to the Form_BeforeUpdate event, which fires at the last possible moment before the record is saved, as opposed to the BeforeInsert event, which fires pretty much as soon as the first character is entered into a new record. Using the BeforeInsert event, in a multi-user scenario, increases the chance of two users starting a record at or near the same time and having the same number assigned to both records.

Even with this precaution the DMax technique remains vulnerable in a multiuser scenario. The problem gets worse with more users and as the number of records grows.

Best practice stores the next number in a one record table. The request for the next number is done by opening a recordset with other users locked out while the number is applied and incremented. A multiple loop of retries is required in case an attempt is made while the table is locked.

The recordset technique is vastly superior and I implore developers to abandon the DMax for this purpose.