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
|