Auto Numbering format

M

Mavis

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
 
K

Ken Sheridan

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/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


Ken Sheridan
Stafford, England
 
M

Mavis

Hi,

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

Thanks
 
M

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?
 
B

BruceM

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.
 
K

Ken Sheridan

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
 
M

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
 
B

BruceM

Bind the text box to the ID field.

Mavis said:
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
Click to expand...
 
C

Chris

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
 
B

BruceM

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.
 
Top