Autonumber

  • Thread starter Thread starter PS
  • Start date Start date
P

PS

Hi,

Anybody can help me?
I need a table to generate autonumber with specific format of number but not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat
 
Hi,

Anybody can help me?
I need a table to generate autonumber with specific format of number but not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat
Click to expand...

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing data, such as a date, in a field along with other
data is generally considered unwise; fields should be "atomic", i.e.
have only one indivisible value. This kind of key should ONLY be used
for compatibility with an existing manual system.

If you are going to use this key, I'd suggest using *two* fields - a
four byte text field for the month and year (I'll call it KeyYYMM) and
an Integer for the sequential portion (KeyN). You'll need to do all
your data entry using a Form - table datasheets don't have any usable
events. In the Form's BeforeInsert event you'll want code like this:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYYMM As String
strYYMM = Format(Date(), "yymm") ' generate 0311, frex
Me!txtKeyYYMM = strYYMM
Me!txtKeyN = 1 + NZ(DMax("KeyN", "yourtable", "[KeyYYMM] = '" & _
strYYMM & "'"))
End Sub

This will sometimes run into problems if two users are simultaneously
creating new records in a shared database; more elaborate schemes can
be devised to avoid such problems.
 
Thank for help!
but i'm a beginner for access, what is the better way to do this for
multiuser enviroment?

John Vinson said:
Hi,

Anybody can help me?
I need a table to generate autonumber with specific format of number but not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat
Click to expand...

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing data, such as a date, in a field along with other
data is generally considered unwise; fields should be "atomic", i.e.
have only one indivisible value. This kind of key should ONLY be used
for compatibility with an existing manual system.

If you are going to use this key, I'd suggest using *two* fields - a
four byte text field for the month and year (I'll call it KeyYYMM) and
an Integer for the sequential portion (KeyN). You'll need to do all
your data entry using a Form - table datasheets don't have any usable
events. In the Form's BeforeInsert event you'll want code like this:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYYMM As String
strYYMM = Format(Date(), "yymm") ' generate 0311, frex
Me!txtKeyYYMM = strYYMM
Me!txtKeyN = 1 + NZ(DMax("KeyN", "yourtable", "[KeyYYMM] = '" & _
strYYMM & "'"))
End Sub

This will sometimes run into problems if two users are simultaneously
creating new records in a shared database; more elaborate schemes can
be devised to avoid such problems.
Click to expand...
 
The better way is to not rely on this. Use an auto number as the key and
keep track of the date.
If they have to see the number xxxx then it can be added in reports etc.

I've never seen a situation where such numbers were *needed* in a computer
program.
They were there because that's the way it was always done.


PS said:
Thank for help!
but i'm a beginner for access, what is the better way to do this for
multiuser enviroment?

but
not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat
Click to expand...

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing data, such as a date, in a field along with other
data is generally considered unwise; fields should be "atomic", i.e.
have only one indivisible value. This kind of key should ONLY be used
for compatibility with an existing manual system.

If you are going to use this key, I'd suggest using *two* fields - a
four byte text field for the month and year (I'll call it KeyYYMM) and
an Integer for the sequential portion (KeyN). You'll need to do all
your data entry using a Form - table datasheets don't have any usable
events. In the Form's BeforeInsert event you'll want code like this:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYYMM As String
strYYMM = Format(Date(), "yymm") ' generate 0311, frex
Me!txtKeyYYMM = strYYMM
Me!txtKeyN = 1 + NZ(DMax("KeyN", "yourtable", "[KeyYYMM] = '" & _
strYYMM & "'"))
End Sub

This will sometimes run into problems if two users are simultaneously
creating new records in a shared database; more elaborate schemes can
be devised to avoid such problems.
Click to expand...
Click to expand...
 
Hi Pat,
That issue is what i had to overcome recently and the following was the
solution i found:

'first get the system date:
sYear = Year(date)
sMonth= Month(date)
'make a string
myStr = sYear & sMonth
'Access gets the number incremented and you add the myStr in the beginning

myStr = myStr & cstr(accessNumber)

'The result is what u need

Hakan

http://www.vbasicmaster.com
 
I agree with the other responses. Depending one why you need the number
(ask yourself if you really need that number or if the it has just been done
that way) usually the best way of getting it is to compute it where it is
needed. Do you really need it as part of the input?

Access can make it up when needed and not bother storing it and the
whole thing is likely to run faster and more reliably than if you input it
in a table.

Also important notice. The Access autonumber does NOT mean consecutive
numbers. I don't think you would want to use it here anyway, but I thought
I would add that.
 
Mike Painter said:
The better way is to not rely on this. Use an auto number as the key and
keep track of the date.
If they have to see the number xxxx then it can be added in reports etc.

I've never seen a situation where such numbers were *needed* in a computer
program.
They were there because that's the way it was always done.


PS said:
Thank for help!
but i'm a beginner for access, what is the better way to do this for
multiuser enviroment?

John Vinson said:
Hi,

Anybody can help me?
I need a table to generate autonumber with specific format of number
Click to expand...
but
not
using access default increment autonumber, for example: 0311-0001 and next
no. 0311-0002 where 0301 mean year month and behind is running no.

Thk,

Pat

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing data, such as a date, in a field along with other
data is generally considered unwise; fields should be "atomic", i.e.
have only one indivisible value. This kind of key should ONLY be used
for compatibility with an existing manual system.

If you are going to use this key, I'd suggest using *two* fields - a
four byte text field for the month and year (I'll call it KeyYYMM) and
an Integer for the sequential portion (KeyN). You'll need to do all
your data entry using a Form - table datasheets don't have any usable
events. In the Form's BeforeInsert event you'll want code like this:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYYMM As String
strYYMM = Format(Date(), "yymm") ' generate 0311, frex
Me!txtKeyYYMM = strYYMM
Me!txtKeyN = 1 + NZ(DMax("KeyN", "yourtable", "[KeyYYMM] = '" & _
strYYMM & "'"))
End Sub

This will sometimes run into problems if two users are simultaneously
creating new records in a shared database; more elaborate schemes can
be devised to avoid such problems.
Click to expand...
Click to expand...
Click to expand...
 
Thank for help!
but i'm a beginner for access, what is the better way to do this for
multiuser enviroment?
Click to expand...

The technique I use is from Getz et al.'s _Microsoft Access Developers
Handbook_ and it's copyrighted, so I can't post it here; basically you
would have a small (one row for one "autonumber") table with a field
for the next available number. Open this table exclusively when you
need a number, grab the value, increment it, and store it back before
releasing the table.
 
Back
Top