Autonumber Manipulation? 1

[OverDrive]

Is there ANY way to manipulate the autonumber field (without addins and subtracting records). I am looking to make the autonumber field (which happens to be my primary key and the default ID field), have a format of at least 4 o 5 digits . . . random placed numbers would work great as well, but I will stick with the small stuff first.

in lamens ---> how do I change the format of the autonumber field to make it a 4 or 5 character string and use a start value of say 1567?

Anyone please?

Chance~

 

[MichaelRed]

Ms. Access '97 does have a way to start the Autonumber at a value other than Zero. I thought it was rather 'kludgy', and never needed to be all that concerned about the starting value. I'guess' that if I had to, I would just create the X number of stupid dummy records before loading real data and then delete them. Problem (of number of digits) solved. Not 'elegant'. Effective.

W/ respect to the "string". This is a NOT Auto[red]Number[/red]. After all, Ms. DID give it the name fro a reason.

On the other hand, I have needed to 'customize' the Record Id to several purposes (CUSTOMER Vanity?), and contributed the faq700-184 which both does the 'job' in one manner, and includes a fair ammount of comment/discussion on what, why and how.

Feel free to take a copy of this and 'roll-your-own' AutoId.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Chance! It took me 15 minutes to find this from an earlier thread I had posted! I've gotta get a grip on this search thing!...For your case use a long integer for the counter field. It won't handle strings = abcde...

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 whatever you want to use it on). On your form new record command:

DoCmd.GoToRecord , , acNewRec
Me![NameOfYourNumberField] = NextNumber(1) '1 as example is the doctype number for "whatever you want to use it on".

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
Set Db = CurrentDb()
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 not be able 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. (Copy the same function, rename and subtract 1 instead!). Now you can manage all your "document numbers" from one table and start them at any point you choose. Enjoy!

So Chance, take the 5 minutes required to build the "DocType" table, toss in some numbers and give it a go. This is your ticket!
Gord
ghubbell@total.net

 

[OverDrive]

Again man . . .

my savior!

Thanks Bro!
Chance~