p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Create Sequential Numbering That Does not Skip (http://p2p.wrox.com/showthread.php?t=29159)

mrslockdown2003 May 10th, 2005 02:54 PM

Create Sequential Numbering That Does not Skip
 
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?


echovue May 10th, 2005 03:07 PM

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

rjweers May 10th, 2005 08:40 PM

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

mrslockdown2003 May 11th, 2005 10:34 AM

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


d-t-r May 11th, 2005 11:45 AM

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

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


All times are GMT -4. The time now is 04:28 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.