Auto Increment from a one number table

[fiaola]

Hi All,

I want to be able to auto increment a number, but I don't wan to use the
AutoInc feature of Access due to problems that i forsee when i have to reset
the number in the future.

What is the best way to do this? Can you post some example? or website that
may have this information?

Thanks for answering.

 

[Beetle]

You can use the DMax function (you'll find info on it in your Access help
file) to return the maximum existing number, and then add one to it.

However, it depends on what you mean by *reset the number*. If you plan on
deleting all the existing records and starting over from 1 (or whatever),
then Dmax should work. On the other hand, if you plan on keeping the existing
records and just *resetting* your numeric field, then you will need some
other field to keep track of which record is the most recent. You may still
be able to use DMax, but you'll have to retrieve the max value from the other
field

 

[John W. Vinson]

Hi All,

I want to be able to auto increment a number, but I don't wan to use the
AutoInc feature of Access due to problems that i forsee when i have to reset
the number in the future.

What is the best way to do this? Can you post some example? or website that
may have this information?

Thanks for answering.

What do you mean by a "one number table"? What are you going to actually do
with this number, and where will it be stored?

John W. Vinson [MVP]

 

[fiaola]

The problem with AutoInc is because the value becomes a PK and If my company
wants to change the number from 100 to 1000, we can't just do it without
compacting and deleting all data.

I want to be able to create a table such as NEXTNUMBER, with only one field
called NextValue. I should be able to READ the current value and add 1 and
do it in an INSERT or NEWRecord event. After that, then i need to update
the NextNumber table.

 

[John W. Vinson]

I want to be able to create a table such as NEXTNUMBER, with only one field
called NextValue. I should be able to READ the current value and add 1 and
do it in an INSERT or NEWRecord event. After that, then i need to update
the NextNumber table.

The code I use was published in Getz, Litwin & Gilbert's supurb _Access 97
Developer's Handbook_. It's been republished in later editions. It's
copyrighted so I won't post the code; but a variant of it has been published
in a Knowledge Base article:

http://support.microsoft.com/kb/210194

John W. Vinson [MVP]

 

[fiaola]

Thanks. That's exactly what i'm looking for.

The code I use was published in Getz, Litwin & Gilbert's supurb _Access 97
Developer's Handbook_. It's been republished in later editions. It's
copyrighted so I won't post the code; but a variant of it has been
published
in a Knowledge Base article:

http://support.microsoft.com/kb/210194

John W. Vinson [MVP]