Print Page | Close Window

Custom Number Demo

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Sheridan, Ken
Forum Description: Samples by Ken Sheridan
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=583
Printed Date: 16 Dec 2017 at 5:58am


Topic: Custom Number Demo
Posted By: KenSheridan
Subject: Custom Number Demo
Date 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
    Else
        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: http://www.rogersaccesslibrary.com/forum/uploads/83/CustomNumber.zip - uploads/83/CustomNumber.zip


-------------
Ken Sheridan, Stafford, England



Print Page | Close Window