Incremented number field NOT using autonumber 1

[murpl]

What would be the best approach to adding a number field to a table that would increment automatically from a predetermined point (not necessarly "1&quot? I intend to display it on a form unedittable by the user to use for purchase orders, etc. The number should always be unique as well.
Thanks, Mike

 

[ghubbell]

I have run a table called tbldoctype.(Does other things as well). Includes An AutoID: "RefType", Description: (PO), (Bill of Lading) whatever, "Counter" which is the current number (default value of 1), "UseCounter" (True/False - set true for PO's). On your PO form new record command:

DoCmd.GoToRecord , , acNewRec
Me![NameOfYourPONumberField] = NextNumber(1) '1 is the doctype number for Po's.

Add this function to a public module:

Public Function NextNumber(RefType As Variant) As Variant
On Error GoTo ErrNN
Dim SQL1 As String, Rs As Recordset, Db as Database
SQL1 = "SELECT tbldoctype.* FROM tbldoctype WHERE (((tbldoctype.RefType)= " & RefType & &quot AND ((tbldoctype.UseCounter)=True))"
Set Rs = db.OpenRecordset(SQL1, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
GoTo ErrNN
End If

NextNumber = Rs!Counter + 1
Rs.Edit
Rs!Counter = NextNumber
Rs.Update
Rs.Close

ExitNN:
Exit Function

ErrNN:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Next document number generation error."
Resume ExitNN
End Function

There are other issues with this: Your user should first be asked if they really want to create the record, and they must be unable to delete it once set. In some cases I cannot do this and run a twin function called PreviousNumber which, you guessed it subtracts one from the count in the event of delete. Now you can manage all your "document numbers from one table and start them at any point you choose. Enjoy!



Gord
ghubbell@total.net

 

[ghubbell]

Mike: I forgot one line you'll need:
Set Db = CurrentDb()
Insert this just below the Dim line. Gord
ghubbell@total.net

 

[murpl]

Excellent! More than I really need but now that the gears are turning mebbe not. Perfect for what I need and then some. Thanks alot for sharing it!

Mike

 

[ghubbell]

Implement it and it'll take care of you well. Thanks Mike! Gord
ghubbell@total.net

 

[7ony]

You can 'seed' an autonumber field by writting a query that Adds one record and sets the value.

Example:
Table: MyTable
Fields: ID Autonumber (Primary key)
Name Text

Do the following Query:
INSERT INTO MyTable ( ID ) SELECT 12 AS NStart;
---
NStart is just the name I gave the 12. Its not important.
You can then delete the record w/ 12 as the value & the next will start at 13...

7ony

(PS. For fun, try 'seeding' w/ a -3 and see what happens at 0.)