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
Printed Date: 23 May 2022 at 2:28pm
Software Version: Web Wiz Forums 12.03 -

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

Print Page | Close Window

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