Autonumber Update differs from record count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form that has an auto number field on and fields that are
populated with default values. My customer does not always complete the form
due to interruptions but the table is updated anyway. The request from the
customer is that the number and record count always match. Does anyone know
how to keep from getting holes in the number sequencing in the table so that
the auto number and record count always match.
 
You cannot do that with an autonumber field. Autonumber fields have this
nasty habit of not releasing a number when a record is deleted or
cancelled out from during creation; the only exception is unused numbers
beyond the last one used, if a Compact and Repair is carried out. So,
assuming your autonumber had gone up to say, 9 at a time, then that
record was deleted or cancelled at creation, if a Compact and Repair is
carried out, then 9 will be used again; if a new record is created
before a Compact and Repair, then it will be assigned number 10, even
though 9 is no longer used, and you'll end up with a gap which you will
never be able to reclaim. This is not a problem, really, since
autonumbers are only meant to provide unique record identifiers, but it
certainly doesn't let you work on the assumption that the next one will
be Max+1. Life being the bitch that it is, record deletions and
cancelled entries happen all the time, so you can't rely on autonumbers
for consecutive numbering.

If all data entry is done through forms, the solution lies in using a
Long Integer field instead, and letting the form assign the next value
each time a new record is created. To do that, use the Default value
property of the control bound to the field, with a formula like:

=DMAx("FieldName", "TableName") + 1

This way, every time a new record is entered, the formula will dwefault
in the Max existing number + 1; if the record is not saved, the default
number won't either, so no hole.

HTH,
Nikos
 
Back
Top