If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Numbering system based on current year
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 |
#2
|
|||
|
|||
Auto Numbering system based on current year
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 "zat" u24642@uwe wrote in message news:63ed0599b5061@uwe... 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 |
#3
|
|||
|
|||
Auto Numbering system based on current year
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 --------------------------- "zat" u24642@uwe wrote in message news:63ed0599b5061@uwe... 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 |
#4
|
|||
|
|||
Auto Numbering system based on current year
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 "zat" wrote: 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 |
#5
|
|||
|
|||
Auto Numbering system based on current year
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 . Yanick wrote: 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 |
#6
|
|||
|
|||
Auto Numbering system based on current year
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. Yanick wrote: 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 |
#7
|
|||
|
|||
Auto Numbering system based on current year
On Fri, 28 Jul 2006 20:18:14 GMT, zat wrote:
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. Yanick wrote: 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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#8
|
|||
|
|||
Auto Numbering system based on current year
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 "zat" wrote: 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. Yanick wrote: 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 |
Thread Tools | |
Display Modes | |
|
|