Autonumbers with year included

[Guest]

Hi, ive been looking at the below code which i got from another discussion.
the code is designed to include the current year within the autonumber. The
trouble is im not sure at all where to put this code into my access 2003
database. i have a table with a field that is autonumber and i have a form
which uses that field to display the autonumber. where would the code go

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 03-0001, 03-0002, etc.
' Seed the first number if other than 0000
'********************************************************************
On Error GoTo Error_Handler

Dim intNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by
[CaseNum];")

If Not rs.EOF Then
rs.MoveLast
If Left(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 3)) + 1
Else
intNumber = 1
End If
End If

DateNum = Format(Year(Date),"yy") & "-" & Format(intNumber, "0000")

With rs
.AddNew
!CaseNum = DateNum
.Update
End With

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler: 'If someone is editing this record trap the error
Dim intRetry As Integer
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Err.Number, vbOKOnly, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume Exit_Here
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Jeff Boyce]

It may be a matter of semantics. In Access, an Autonumber is generated ONLY
by Access -- you have no control over it, and cannot "include" a year in it.

I believe Arvin's code sample was intended to help you come up with a
replacement for the Autonumber.

Note: if you have both a year and a numbering scheme, you need two fields,
not one. You can always concatenate the two values together in a query to
display on a form, but you really don't want to store more than one (kind
of) fact in one field.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

You should not be including the year in an autonumber field.