Create a manual Auto Number feild

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

Guest

Hi,

I have a number field as part of a form. When I create a
new record, I'd like to increment the field by 1 based on
the highest number in the record set.

I do not want to use Autonumber, as if someone starts a
record, and then abandons it, I'll loose the number in
the history.

Is there any way to set the default value of the field to
increment by 1 based on the highest number in the table's
column?

HELP!
 
Hi,

I have a number field as part of a form. When I create a
new record, I'd like to increment the field by 1 based on
the highest number in the record set.

I do not want to use Autonumber, as if someone starts a
record, and then abandons it, I'll loose the number in
the history.

Is there any way to set the default value of the field to
increment by 1 based on the highest number in the table's
column?
Click to expand...

In a form (not the table), you could use a default value property of...

=Nz(DMax("YourField", "YourTable"),0)+1

....however; this will only work reliably in a single view form (not
continuous or datasheet) and only if one person will ever be adding records
at the same time.

I use the BeforeUpdate code event as that calculates the value required and
them immediately commits the record to the table so the window of time
where two users could grab the same value is very small. It also works
with any form format. You need an If-Then block to ensure that you only
apply the value to records that do not already have one since BeforeUpdate
can fire many times in the life of a record

If IsNull(Me!ID) = True Then
Me!ID = Nz(DMax("ID", "YourTable"),0)+1
End If
 
The easiest way to create a custom counter is using the Dmax function where
you grab the highest existing value from the table and then increment it.
This method is fine in some environments but not in a multiuser situation
where 2 or more people are simultaneously entering records into the same
table.

For example: You have a table tblCustomer and you want to increment Custid
without using an Autonum field. The following will get the current max value
of Custid, increment it by 1 and then put the new value into the control
me.custid:

me.custid=nz(dmax("Custid","tblCustomer"),0)+1

Another method that works well for the multiuser situation is create a table
which has only one row. This table holds the next available number for the
counter. Build a function, GetInvoiceNum (or whatever you want to call it).
This function will open the table with the dbDenyRead option, which prevents
other users from opening the table until this instance of the function
closes it. The function gets the next value (the return value of the
function) and the increments it and updates the table. In the error handling
of the function, if the table can't be opened because it is locked, your
function should wait and then try again.

For more info on one method for this see:

ACC2000: How to Create a Multiuser Custom Counter ID: Q210194
http://support.microsoft.com/default.aspx?scid=kb;[LN];210194

Another resource is the Access 20XX Developers Handbook (Volume 2), Litwin,
Getz and Gilbert which has some code for this which you can probably use as
is in your project.
 
I do not want to use Autonumber, as if someone starts a
record, and then abandons it, I'll loose the number in
the history.
Click to expand...

What happens if Eric starts a record (107), then Anna starts another record
(108), and then Eric backs out because he has to go for tea? How do you
plan to get record 107 back into the sequence?

Tim F
 
Back
Top