Auto Numbering system based on current year

[zat]

I am trying to do an auto correspondence number based on the current year and
have the counter start at 001 so the first correspondence number would be
2006-001 and the next record number would be 2006-002. In 2007 it would
start over with 2007-001. This numbering system would be activated when the
user selects a button (Command33). The table name is tblSyscounters with
fields: Pvar (unique key), YearPart (Current year), Countr (squential counter)
The following code is giving me a User defined-type not defined error that
points to Dim rs As DAO.Recordset. Does anyone have a clue what I'm doing
wrong?

Public Function GetNextNumber() As String

Dim rs As DAO.Recordset
Dim Year As Integer
Dim CompareYear As Long
Dim CurCount As Long

Set rs = db.OpenRecordset("tblSyscounters", dbOpenDynaset)

CompareYear = DatePart("yyyy", Date)
With rs

FindFirst "Key = 'Pvar'"

If !YearPart <> CompareYear Then
!Countr = 0
!YearPart = CompareYear
End If

!Countr = !Countr + 1
Update
Year = !YearPart - 2000
CurCount = !Countr
Close
End With
GoTo XIT

XIT:
GetNextNumber = "V" & Right$("00" & Year, 2) & "-" & Right$("00000" &
CurCount, 6)
End Function

The Event procedure for the button is:



Private Sub Command33_Click()
ingOutNum = GetNextNumber()
End Sub

 

[Jeff Boyce]

Take a quick look at mvps.org for "Custom Autonumber" (it isn't really an
"autonumber", it just plays one in the movies). This should give you a
simple approach.

By the way, relational database design would call for two separate fields,
rather than stuffing more than one fact into a single field. If your table
held a DateCreated date/time field, and a SequenceNumber field, you could
use a query with the Year([DateCreated]) function to determine the first
part of your "correspondence number", and a function that checks for the
highest used sequence number for the year of today's date, then adds one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham R Seach]

You can do it in a single Text field in the following format: "2006-001"

Year(Date) & "-" &
Format(Nz(DLookup("Mid(myField,6)","Table1","Left(myField,4)=" &
Year(Date)),0)+1,"000")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

I don't know why you receive the error msg but I think I have a simpler
solution for you auto number assignement.

First, I don't see the need store each part of your unique identifier into 2
table field.

Here is how I do it.

------------------------------
Public Function GetNextNumber() As String

Dim MyAutoNumber as string

If Not IsNull(DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")) Then
MyAutoNumber = DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")
MyAutoNumber = year(now()) & "-" & Format(Int(Right(MyAutoNumber, 3)) +
1, "00#")
Else
MyAutoNumber = year(Now()) & "-001"
End If

GetNextNumber = MyAutoNumber

End Function

 

[zat]

Yanick,
Thank you, Thank you. It works like a charm. I've been banging my head
against the wall over this one, not sure why I hadn't asked for help before.
You've made my day .

I don't know why you receive the error msg but I think I have a simpler
solution for you auto number assignement.

First, I don't see the need store each part of your unique identifier into 2
table field.

Here is how I do it.

------------------------------
Public Function GetNextNumber() As String

Dim MyAutoNumber as string

If Not IsNull(DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")) Then
MyAutoNumber = DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")
MyAutoNumber = year(now()) & "-" & Format(Int(Right(MyAutoNumber, 3)) +
1, "00#")
Else
MyAutoNumber = year(Now()) & "-001"
End If

GetNextNumber = MyAutoNumber

End Function

--------------------------------

There it is. No need to use recordset. It should solve your problem.

Yanick

I am trying to do an auto correspondence number based on the current year and
have the counter start at 001 so the first correspondence number would be

[quoted text clipped - 43 lines]

ingOutNum = GetNextNumber()
End Sub

 

[zat]

Okay, like I said this works great but there always seems to be one exception.
When I select the button for a new number it loads into the field perfectly.
If I select the button again nothing changes - perfect, just what I want.
But if I'm one an existing record and I select the button the next number
overrides the existing number. Is there anyway to lock the number once it is
applied to the field? I've modified the properties so the number cannot be
edited but it can be overridden with the button. Thanks for any help you can
give me.

I don't know why you receive the error msg but I think I have a simpler
solution for you auto number assignement.

First, I don't see the need store each part of your unique identifier into 2
table field.

Here is how I do it.

------------------------------
Public Function GetNextNumber() As String

Dim MyAutoNumber as string

If Not IsNull(DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")) Then
MyAutoNumber = DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")
MyAutoNumber = year(now()) & "-" & Format(Int(Right(MyAutoNumber, 3)) +
1, "00#")
Else
MyAutoNumber = year(Now()) & "-001"
End If

GetNextNumber = MyAutoNumber

End Function

--------------------------------

There it is. No need to use recordset. It should solve your problem.

Yanick

I am trying to do an auto correspondence number based on the current year and
have the counter start at 001 so the first correspondence number would be

[quoted text clipped - 43 lines]

ingOutNum = GetNextNumber()
End Sub

 

[fredg]

Okay, like I said this works great but there always seems to be one exception.
When I select the button for a new number it loads into the field perfectly.
If I select the button again nothing changes - perfect, just what I want.
But if I'm one an existing record and I select the button the next number
overrides the existing number. Is there anyway to lock the number once it is
applied to the field? I've modified the properties so the number cannot be
edited but it can be overridden with the button. Thanks for any help you can
give me.

I don't know why you receive the error msg but I think I have a simpler
solution for you auto number assignement.

First, I don't see the need store each part of your unique identifier into 2
table field.

Here is how I do it.

------------------------------
Public Function GetNextNumber() As String

Dim MyAutoNumber as string

If Not IsNull(DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")) Then
MyAutoNumber = DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")
MyAutoNumber = year(now()) & "-" & Format(Int(Right(MyAutoNumber, 3)) +
1, "00#")
Else
MyAutoNumber = year(Now()) & "-001"
End If

GetNextNumber = MyAutoNumber

End Function

--------------------------------

There it is. No need to use recordset. It should solve your problem.

Yanick

I am trying to do an auto correspondence number based on the current year and
have the counter start at 001 so the first correspondence number would be

[quoted text clipped - 43 lines]

ingOutNum = GetNextNumber()
End Sub

If Me.NewRecord then
' Your Code here
End if

 

[Guest]

Add an IF prior the attribution of a new number.


if isnull(me.TheFieldContainingTheNumber.value) or
me.TheFieldContainingTheNumber.value = "" then
Create a newNumber
end if

Yanick

Okay, like I said this works great but there always seems to be one exception.
When I select the button for a new number it loads into the field perfectly.
If I select the button again nothing changes - perfect, just what I want.
But if I'm one an existing record and I select the button the next number
overrides the existing number. Is there anyway to lock the number once it is
applied to the field? I've modified the properties so the number cannot be
edited but it can be overridden with the button. Thanks for any help you can
give me.

I don't know why you receive the error msg but I think I have a simpler
solution for you auto number assignement.

First, I don't see the need store each part of your unique identifier into 2
table field.

Here is how I do it.

------------------------------
Public Function GetNextNumber() As String

Dim MyAutoNumber as string

If Not IsNull(DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")) Then
MyAutoNumber = DMax("[YourAutoNumberField]", "TheTableWhereItIsStored")
MyAutoNumber = year(now()) & "-" & Format(Int(Right(MyAutoNumber, 3)) +
1, "00#")
Else
MyAutoNumber = year(Now()) & "-001"
End If

GetNextNumber = MyAutoNumber

End Function

--------------------------------

There it is. No need to use recordset. It should solve your problem.

Yanick

I am trying to do an auto correspondence number based on the current year and
have the counter start at 001 so the first correspondence number would be

[quoted text clipped - 43 lines]

ingOutNum = GetNextNumber()
End Sub