Auto Number with year

[hatched850]

:help Anyone know a code that will create and auto number for a new record with a year ie 2006-01, 2006-02, and so on.

[OBP]

Hello again. the system that I use holds the currently last Record Number (less the year) in a seperate table as well as in the last record. I have some VBA code that opens that table to extract the current number, increment by 1 and add it to the current year in a string format.
If you wish to reset the count back to 1 for the start of a new year i.e. 2007-01 then you also need to store the current year in a table and compare it with todays date, if it is different then cahnge it to the new year and reset the counter to 1.
Which mwthod would you like to use?
ps it doesn't have to be stored in a seperate table, I just like to it that way.

[hatched850]

I will need to reset the counter each year.

[OBP]

What is the name of the field that is to hold this autonumber.

[hatched850]

requestId field in the publication request table

[OBP]

First of all you will have to change your current table "Autonumber" field from an autonumber to a text field of 10 characters length.
Put the following code in the "On Current" Event procedure of your Publications Requests form.

Private Sub Form_Current()
Dim recordyear As String, zeroes As String, rs As Object
zeroes = "0000"
If Me.NewRecord Then
Set rs = Me.Recordset.Clone
rs.MoveLast
recordyear = rs.requestId.Value
rs.Close
If Left(recordyear, 4) <> Right(Date, 4) Then
Me.requestId = Right(Date, 4) & "-0001"
Exit Sub
Else
recordyear = Str((Right(recordyear, 4)) + 1)
recordyear = Right(recordyear, (Len(recordyear) - 1))
If Len(recordyear) < Len(zeroes) Then
recordyear = Left(zeroes, (Len(zeroes) - Len(recordyear))) & recordyear

End If
Me.requestId = Right(Date, 4) & "-" & recordyear
End If

End If
End Sub

You will need to change your current records to the new 2006-0001 format, just change the first one and delete the other records.


I have added to the original database that I posted on your other thread and attached it to this one.