Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > Other Developer Libraries > Sheridan, Ken
  New Posts New Posts RSS Feed - Custom Number Demo
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

Custom Number Demo

 Post Reply Post Reply
Message Reverse Sort Order
KenSheridan View Drop Down

Joined: 25 Apr 2012
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote KenSheridan Quote  Post ReplyReply Direct Link To This Post Topic: Custom Number Demo
    Posted: 25 Apr 2012 at 7:09am
Sample to create a customer Autonumber function.
I've amended Roger's to allow numbers to be seeded, by changing his GetProductID() function in the product_2 form's module as follows:

Private Function GetProductID()

    Dim lngProductID As Long
    Dim lngSeedID As Long
    lngProductID = Nz(Dmax("ProductID", "Product"), 0) + 1
    lngSeedID = Nz(Dlookup("Seed", "Seeds"), 0)
    If lngSeedID <= lngProductID Then
        GetProductID = lngProductID
        GetProductID = lngSeedID
    End If
End Function

This requires the addition of a one-row table, Seeds, to the database, with a single column, Seed, of long integer number data type.  Enter a row with a zero value to start with.  The number you wish to 'seed' is entered into this table, for which a simple form can be designed, setting its AllowAdditions and AllowDeletions properties to False (No) so that only the one existing row can be edited.   To open this form I've added a button to the Product2 form with the following code in its Click event procedure:

    DoCmd.OpenForm "frmSeeds", WindowMode:=acDialog
    Me.ProductID.DefaultValue = """" & GetProductID & """"

This amendment to Roger's solution makes mine pretty much redundant.  There could be some situations where mine is more bullet-proof, principally that the user will see the new number when they begin to insert the new record and it will not change, whereas with Roger's, if there is a conflict the number will change for any user who is not the first to save the record with the new number; but by and large Roger's solution, amended as above to allow seeding, will be fine and is a lot simpler to implement.

Download Access MDB and ACCDB: uploads/83/
Ken Sheridan, Stafford, England
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.