I am trying to create a field that will automatically generate a sequential number (one that will not skip integers!). I would like to use little code if at all possible. Autonumber in Access does not work for what I am trying to do. For example... I have a record #1 then I want the next record to be #2, however if I hit escape within the new record (#2), the next number generated is #3. I need it to always be in sync. Any suggestions?

I used to shy away from autonumber for this exact reason, but if you can figure out a way to use it, it probably be best for you in the long run.

However if it definitely won't work for you...

I used this code to figure out the next number in the sequence
where strPrimary key is the field Name of the sequential number
and strTable is the table in question.

strSQL = "SELECT * FROM " & strTable & " ORDER BY " & strPrimaryKey
    rs.Open strSQL, cn

    If rs.EOF And rs.BOF Then
        theKey = 1
    Else
        rs.MoveLast
        theKey = rs.Fields(strPrimaryKey) + 1
    End If

If you don't want the number to be added until you save the record, use an unbound form, and have a save button. The on Click event then pulls each of the values, gets the sequential number and then inserts into your table. This way if you exit without saving the record, nothing is added.

Hope that helps.

Mike

Mike
EchoVue.com

First of all, I'm going to assume that you need sequential numbering for something other than a foreign key. If it is a foreign key I have to ask what difference it makes?

Otherwise, an even more simple solution to sequential numbering (assuming you haven't gone back and deleted a number in the middle of a sequence) is:

NewNumber = DMax("number_field","table") + 1

Is that "as little code as possible"?

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks for your suggestions! I ended up using the following after looking at rjweers suggestion, =nz(Max([SequentialNumber]),0)+1.

Again, thanks for your help!!!:D

Good solution. I have a co-worker who's very likely to use this very solution.

If the exceptions outnumber the rules, why are they called rules?