Customize Autonumber

  • Thread starter Thread starter NicoleDBS
  • Start date Start date
N

NicoleDBS

Hello,
I am trying to make an autonumber have IN(yy)-nnn, I have used the forms
BeforeInsert function to write the code, but nothing I have tried is working.
Any help out there would be appreciated.
 
Hello,
I am trying to make an autonumber have IN(yy)-nnn, I have used the forms
BeforeInsert function to write the code, but nothing I have tried is working.
Any help out there would be appreciated.
Click to expand...

Correct the error in your code. If you would like help doing so please... post
the code, and an example of what you want the number (an Autonumber field will
NOT work) to look like. Do you mean literally the letters IN, or something
else?
 
NicoleDBS said:
Hello,
I am trying to make an autonumber have IN(yy)-nnn, I have used the
forms BeforeInsert function to write the code, but nothing I have
tried is working. Any help out there would be appreciated.
Click to expand...

If "IN" is always part of the number, there is no need to store it and it
makes things much harder.
Store the date if you are not and the "yy" which I guess is the year is a
lot easier and does not have to be stored again.
Is "nnn" a sequential number?
will it ever go past 999?
Is 000 a valid number?
What code are you using now to generate it?
I would use a separate table to hold the number as this makes starting ove
in the next year easy.

Format the date to "yy"
Use DMax to find the highest date
If it is a new year then
YourCounter = 0 or 1
else
Use DLookup to get the number from the table
end if

doCmd.RunSql "UPDATE YourTableName SET YourTableName.Yourcounter = Old
Value + 1;"

Then concatinate and display the results.
Use a real autonumber as the key index

Now somebody can come to you and ask for all "567"'s because they can't
remember the year.

Before I would do this I would try to get rid of it completely. Chances are
it is left over from file cabinet days and is of little value any more.
 
Back
Top