Easier way to start AutoNumber at XXXX

[Guest]

Hi guys & gals.

I've searched the community on creating your own unique ID number, but each
time I find something close to what I want a database normalization rule pops
up. What is this database normalization rule? I get that it's about not
storing two forms of data in the same field, but why can it be done? I'm not
looking to replicate or do anything strenuous on the system, just uniquely ID
our rows with customization. Ultimately I would love to use the years last
two digits, and then a 6 digit code ("04000001") to id our rows, but that
doesn't seem possible.

I know that I could create 2 columns, then concate the strings to one using
a SQL query of:
SELECT myTable.Year, myTable.Number, Right([Year], 2) & [Number] AS myNewID
FROM myDatabase;

But ultimately I want it to be done w/o use of queries and on the fly and
incremented. Any ideas?

Thanks for the help.

~Brett

 

[John Vinson]

Hi guys & gals.

I've searched the community on creating your own unique ID number, but each
time I find something close to what I want a database normalization rule pops
up. What is this database normalization rule? I get that it's about not
storing two forms of data in the same field, but why can it be done? I'm not
looking to replicate or do anything strenuous on the system, just uniquely ID
our rows with customization. Ultimately I would love to use the years last
two digits, and then a 6 digit code ("04000001") to id our rows, but that
doesn't seem possible.

It's possible. It's just not a good idea, generally! "Intelligent"
keys containing two different types of data should be used ONLY for
compatibility with a long-standing manual system.

I know that I could create 2 columns, then concate the strings to one using
a SQL query of:
SELECT myTable.Year, myTable.Number, Right([Year], 2) & [Number] AS myNewID
FROM myDatabase;

That's a good choice. You can also set the Control Source of a form or
report textbox to the same expression. For the zeros, tweak it a bit:

Right([IDYear], 2) & Format([IDSeq], "000000")

But ultimately I want it to be done w/o use of queries and on the fly and
incremented. Any ideas?

Well, don't use Year or Number as fieldnames - they are reserved words
and Access *will* get confused. I'll use IDYear and IDSeq in my
example code. Do your data entry through a Form (table datasheets
don't have any usable events). In the Form's BeforeInsert event put
code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtIDYear = Year(Date())
Me!txtIDSeq = NZ(DMax("[IDSeq]", "[yourtablename]", "[IDYear] = " _
& Year(Date()))) + 1
If Me!txtIDSeq > 999999 Then
MsgBox "Go home, you're done for the year - no more ID's allowed"
Cancel = True
End If
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps