Hi Richard

How can I get the next sequential Autonumber in code.  I tried using Dmax and adding 1 to it which worked great until the last record was deleted, then the wrong number was entered in my ID field (all fields and the form are unbound). Thanks!

---

Reply from Richard Rost:

I don't believe it's possible to calculate the next AutoNumber. I've never done it before, and I would have to research it for you to be absolutely sure, but AutoNumbers are only meant for INTERNAL Access purposes only. YOU should never really care what they are, except for creating table relationships. Access manages them itself.

You can find out what the largest CURRENT Autonumber is by using DMAX like you said, but let's say you insert 10 records then delete 3. If you DMAX now, you'll get 7, but the next Autonumber will be 11.

I did a quick Google search and most of the forums that I read on a regular basis seem to agree with me. There is the option of COMPACTING your database (which will bring down your Autonumbers to the next highest number) but you'd have to do that EVERY time.