Autonumber type field

[Aurora]

I am using access 2000.

I want to make a database with a data field [File#] to be
have a number automatically assigned -- such as 1000,
1001, 1002, 1003 etc.
I have been told that to use the autonumber feature is not
a good idea for this type of field. How do I do this
another way????

Can someone please advise or point me in the right
direction?

Aurora

 

[Kevin3NF]

If you need for that field to always take the next highest number, use a
DMAX function to create the value on new records. Autonumber sometimes
skips around, and can even be be negative (IIRC).

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Aurora]

How and where do I use the DMAX function?
Aurora

-----Original Message-----
If you need for that field to always take the next highest number, use a
DMAX function to create the value on new records. Autonumber sometimes
skips around, and can even be be negative (IIRC).

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

I am using access 2000.

I want to make a database with a data field [File#] to be
have a number automatically assigned -- such as 1000,
1001, 1002, 1003 etc.
I have been told that to use the autonumber feature is not
a good idea for this type of field. How do I do this
another way????

Can someone please advise or point me in the right
direction?

Aurora

.

 

[Kevin3NF]

I would create a number field in the table, and of form with a textbox
pointed to that field.

in the default property for the textbox, something like:

=DMax("MyID", "MyTable")+1

My syntax may be off a hair, but the idea is correct.

This gets the highest value in the "MyID" field (or whatever you call it in
your table), for the table, adds 1 to it, and makes that the new ID value.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

How and where do I use the DMAX function?
Aurora

-----Original Message-----
If you need for that field to always take the next highest number, use a
DMAX function to create the value on new records. Autonumber sometimes
skips around, and can even be be negative (IIRC).

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

I am using access 2000.

I want to make a database with a data field [File#] to be
have a number automatically assigned -- such as 1000,
1001, 1002, 1003 etc.
I have been told that to use the autonumber feature is not
a good idea for this type of field. How do I do this
another way????

Can someone please advise or point me in the right
direction?

Aurora

.

 

[Armen Stein]

I would create a number field in the table, and of form with a textbox
pointed to that field.

in the default property for the textbox, something like:

=DMax("MyID", "MyTable")+1

My syntax may be off a hair, but the idea is correct.

This gets the highest value in the "MyID" field (or whatever you call it in
your table), for the table, adds 1 to it, and makes that the new ID value.

This approach will work in most situations, but in heavy multi-user
environments it's technically possible to have two records get the same
ID value. This would happen in the unlikely event that two users create
a new record at the same time, getting the same DMAX value, before the
new record is added.

If this is a concern, then you can put a NextAvailableID field in
another table, and increment it during a locked update. This ensures
that the second user will not get the same number.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[Kevin3NF]

Good point

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.