Auto Numbering format

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Hi,

Where can i find the Before Insert. And how can i show the auto number in
the form?

Thanks

"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running number
every year? For example the last ID number for year 2009 is 2009888, will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

ID is the field in the form's Record Source in which the number is stored.
Change it to match the name of your table field. Surround the name in
square brackets if it contains anything other than letters, numbers, and
underscores.

Add Option Explicit at the top of the code window, directly below Option
Compare Database, if it is not already there. Compile the code by clicking
Debug Compile.

The code as suggested will start over from 001 each year.

"Mavis" wrote in message
...
Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error
prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running number
every year? For example the last ID number for year 2009 is 2009888, will
the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first
user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger
Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Also, its important to understand that the ID field (or whatever you've
called it) is not an autonumber but a straightforward long integer number
data type, which is the default when you select 'number' as the data type in
table design view.

Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running number
every year? For example the last ID number for year 2009 is 2009888, will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Hi All,

I had did the below.

1. add in a unbound text box for the ID
2. Add a field "ID" in the table and the data type is "Number"
3. Inside the code provided into the Beforeinsert.

Result: The number genrated and shows in the unbound text box but the number
is not insert into the table and when i add in more than one record the
number genrated remain the same. "2009001"

Please help me.

Thanks


"Ken Sheridan" wrote:

Also, its important to understand that the ID field (or whatever you've
called it) is not an autonumber but a straightforward long integer number
data type, which is the default when you select 'number' as the data type in
table design view.

Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running number
every year? For example the last ID number for year 2009 is 2009888, will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users are
adding a new record simultaneously. What would happen is that the first user
to save the record could do so successfully, but the other user(s) would
experience an error by virtue of the primary key violation. Roger Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Bind the text box to the ID field.

"Mavis" wrote in message
...
Hi All,

I had did the below.

1. add in a unbound text box for the ID
2. Add a field "ID" in the table and the data type is "Number"
3. Inside the code provided into the Beforeinsert.

Result: The number genrated and shows in the unbound text box but the
number
is not insert into the table and when i add in more than one record the
number genrated remain the same. "2009001"

Please help me.

Thanks


"Ken Sheridan" wrote:

Also, its important to understand that the ID field (or whatever you've
called it) is not an autonumber but a straightforward long integer number
data type, which is the default when you select 'number' as the data type
in
table design view.

Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error
prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in
yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running
number
every year? For example the last ID number for year 2009 is 2009888,
will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert
event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users
are
adding a new record simultaneously. What would happen is that the
first user
to save the record could do so successfully, but the other user(s)
would
experience an error by virtue of the primary key violation. Roger
Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

Hello,

I have used the code for the auto numbering in my form and it works fine:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(Medicatie_ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("Medicatie_ID", "tblMedicatie_Transacties", strCriteria)

If IsNull(varLastNumber) Then
Me.Medicatie_ID = intCurrentYear & "001"
Else
Me.Medicatie_ID = varLastNumber + 1

End If
End Sub


Question:

Does it resets itself yearly?
Is it possible to change the year to: year, month and date like this:
090506001?

Can any one help me with the code?

Thanks

"BruceM" wrote:

Bind the text box to the ID field.

"Mavis" wrote in message
...
Hi All,

I had did the below.

1. add in a unbound text box for the ID
2. Add a field "ID" in the table and the data type is "Number"
3. Inside the code provided into the Beforeinsert.

Result: The number genrated and shows in the unbound text box but the
number
is not insert into the table and when i add in more than one record the
number genrated remain the same. "2009001"

Please help me.

Thanks


"Ken Sheridan" wrote:

Also, its important to understand that the ID field (or whatever you've
called it) is not an autonumber but a straightforward long integer number
data type, which is the default when you select 'number' as the data type
in
table design view.

Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I had follow your instruction insert the below code in the BeforeInsert
Event but when i type to enter information in the form the below error
prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in
yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running
number
every year? For example the last ID number for year 2009 is 2009888,
will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the BeforeInsert
event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a multi-user
environment on a network conflicts could arise if two or more users
are
adding a new record simultaneously. What would happen is that the
first user
to save the record could do so successfully, but the other user(s)
would
experience an error by virtue of the primary key violation. Roger
Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis

I think you would do better to store the date and the incrementing number
separately. Your code could then be:

Me.Medicatie_ID = Nz(DMax("Medicatie_ID","tblMedicatie_Transacties", _
"DateField = " & Date),0) + 1

You may need to delimit the date value:

Me.Medicatie_ID = Nz(DMax("Medicatie_ID","tblMedicatie_Transacties", _
"DateField = #" & Date & "#"),0) + 1

For displaying, set the Control Source of an unbound text box to:

= Format(Date(),"yymmdd") & Format(Medicatie_ID,"000")

To store the entire number as it seems you are doing now you would need to
do something like format the date yymmdd, concatenate it with "001", convert
the result to a number, add 1, then format it to include a leading zero if
necessary. I can provide some more information if you decide you must go
that route, but I don't have the time right now to describe it.

I recommend incrementing the ID number separately from the date. The only
potential issue is that if ID is the primary key you will either need to add
a primary key (autonumber, perhaps) or use a multi-field primary key.

"Chris" wrote in message
...
Hello,

I have used the code for the auto numbering in my form and it works fine:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(Medicatie_ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("Medicatie_ID", "tblMedicatie_Transacties",
strCriteria)

If IsNull(varLastNumber) Then
Me.Medicatie_ID = intCurrentYear & "001"
Else
Me.Medicatie_ID = varLastNumber + 1

End If
End Sub


Question:

Does it resets itself yearly?
Is it possible to change the year to: year, month and date like this:
090506001?

Can any one help me with the code?

Thanks

"BruceM" wrote:

Bind the text box to the ID field.

"Mavis" wrote in message
...
Hi All,

I had did the below.

1. add in a unbound text box for the ID
2. Add a field "ID" in the table and the data type is "Number"
3. Inside the code provided into the Beforeinsert.

Result: The number genrated and shows in the unbound text box but the
number
is not insert into the table and when i add in more than one record the
number genrated remain the same. "2009001"

Please help me.

Thanks


"Ken Sheridan" wrote:

Also, its important to understand that the ID field (or whatever
you've
called it) is not an autonumber but a straightforward long integer
number
data type, which is the default when you select 'number' as the data
type
in
table design view.

Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I had follow your instruction insert the below code in the
BeforeInsert
Event but when i type to enter information in the form the below
error
prompt.

"Compile Error:
Method Or data member not found"

"Private Sub Form_BeforeInsert(Cancel As Integer)" is hoghlight in
yellow
and Me.ID is highlight in blue.

I have another question, will your method help to renew for running
number
every year? For example the last ID number for year 2009 is 2009888,
will the
first number in year 2010 be 2010001 or 2010889?


"Ken Sheridan" wrote:

You can do this in a form, not at table level. In the
BeforeInsert
event
procedure of a form bound to the table put the following code:

Dim strCriteria As String
Dim intCurrentYear As Integer
Dim varLastNumber As Variant

intCurrentYear = Year(VBA.Date)
strCriteria = "Left(ID,4) = """ & intCurrentYear & """"

varLastNumber = DMax("ID", "YourTableName", strCriteria)

If IsNull(varLastNumber) Then
Me.ID = intCurrentYear & "001"
Else
Me.ID = varLastNumber + 1
End If

This will work fine in a single user environment, but in a
multi-user
environment on a network conflicts could arise if two or more
users
are
adding a new record simultaneously. What would happen is that the
first user
to save the record could do so successfully, but the other user(s)
would
experience an error by virtue of the primary key violation. Roger
Carlson
has a solution to this at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


Ken Sheridan
Stafford, England

"Mavis" wrote:

Hi,

I need to have the ID to have a certian format like"2009001".
2009 will be the year and follow by the running number.
What is the simplest way to do so. I am new with access 2007.

Thanks,
Mavis