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
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