Fi69
asked on
Automatic sequential number stored in word template to reset at turn of calendar year
Hi experts
I have an invoice template with a sequential number. The code below is sitting in Auto_New.
When a new invoice is generated the last invoice number (variable defined as AutoNumber) is picked up from the Template, it is increased by one, and this number becomes the invoice number which is then stored back in the template.
This all works well, but now the client wants me to have the numbering reset at the turn of the calendar year. So the numbering will look like this:
2010_01
2010_02
2010_03
etc
Then as of 1 January 2011 (for example) it will change to 2011 and default back to 01.
2011_01.
2011_02
2011_03
etc
Would an expert out there kindly guide me on how I go about doing this.
I know how to have the date_number but I'm unsure of how I go about resetting the number, and the template knows it is back to 01 for the first occasion after the turn of the calendar year. I just can't seem to think of what to do.
I have an invoice template with a sequential number. The code below is sitting in Auto_New.
When a new invoice is generated the last invoice number (variable defined as AutoNumber) is picked up from the Template, it is increased by one, and this number becomes the invoice number which is then stored back in the template.
This all works well, but now the client wants me to have the numbering reset at the turn of the calendar year. So the numbering will look like this:
2010_01
2010_02
2010_03
etc
Then as of 1 January 2011 (for example) it will change to 2011 and default back to 01.
2011_01.
2011_02
2011_03
etc
Would an expert out there kindly guide me on how I go about doing this.
I know how to have the date_number but I'm unsure of how I go about resetting the number, and the template knows it is back to 01 for the first occasion after the turn of the calendar year. I just can't seem to think of what to do.
'Find the last invoice number created
AutoNumber = AttachedTemplate.CustomDocumentProperties("xInvoiceLast")
'Create a new invoice number
AutoNumber = AutoNumber + 1
'Save this invoice number to the template
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = AutoNumber
frmInvoice.txtInvoice = Format$(Date, "yyyy") & "_" & Format(AutoNumber, "00")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looks pretty much it to me. :)
Dim CurrentYear As Date
TemplateYear = AttachedTemplate.CustomDoc umentPrope rties("xIn voiceYear" ).Value
CurrentYear = Year(Now)
If CurrentYear > TemplateYear then
AttachedTemplate.CustomDoc umentPrope rties("xIn voiceLast" ) = "0"
AttachedTemplate.CustomDoc umentPrope rties("xIn voiceYear" ) = CurrentYear
end if
That should do it (just wrote this in the reply so haven't tested this ...)
TemplateYear = AttachedTemplate.CustomDoc
CurrentYear = Year(Now)
If CurrentYear > TemplateYear then
AttachedTemplate.CustomDoc
AttachedTemplate.CustomDoc
end if
That should do it (just wrote this in the reply so haven't tested this ...)
ASKER
Thanks wobbled. I have one problem in defining the date correctly for the year stored in the custom document properties ("xInvoiceYear"). It sees my entry of 2008 (or was it 2009) as 2/07/1905.
I tried changing the variable to String, but then the calculation doesn't work. I think I've got to compare a date with a date. Do you know how I can define the stored value as a date in the format of yyyy?
If I get the fixed I'm confident this approach will work.
I tried changing the variable to String, but then the calculation doesn't work. I think I've got to compare a date with a date. Do you know how I can define the stored value as a date in the format of yyyy?
If I get the fixed I'm confident this approach will work.
Dim myYear, autoYear As Date
myYear = Format$(Date, "yyyy")
'Check Year and Reset if change in calendar year
autoYear = AttachedTemplate.CustomDocumentProperties("xInvoiceYear")
If myYear > autoYear Then
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = "0"
AttachedTemplate.CustomDocumentProperties("xInvoiceYear") = myYear
End If
'Find the last invoice number created
AutoNumber = AttachedTemplate.CustomDocumentProperties("xInvoiceLast")
'Create a new invoice number
AutoNumber = AutoNumber + 1
'Save this invoice number to the template
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = AutoNumber
frmInvoice.txtInvoice = autoYear & "_" & Format(AutoNumber, "00")
frmInvoice.txtDate = Format$(Date, "d mmmm yyyy")
You need to define variable type in VBA, so you need to put it like this:
Dim myYear As Date, autoYear As Date
Then it should work - I tested it with some simple stuff, but try this below with the CDate forcing the string to become a date
myYear = Format$(Date, "yyyy")
autoYear = CDate(AttachedTemplate.Cus tomDocumen tPropertie s("xInvoic eYear"))
Dim myYear As Date, autoYear As Date
Then it should work - I tested it with some simple stuff, but try this below with the CDate forcing the string to become a date
myYear = Format$(Date, "yyyy")
autoYear = CDate(AttachedTemplate.Cus
ASKER
That is so weird, when I step through the code it turns the value to
myYear = 2/07/1905
autoYear = 30/06/1905 (which is the year 2008)
That's not correct is it? It should be showing as yyyy?
myYear = 2/07/1905
autoYear = 30/06/1905 (which is the year 2008)
That's not correct is it? It should be showing as yyyy?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you wobbled. That's working perfectly now. Thanks for helping me!
Dim MyYear As String
Dim autoYear As String
MyYear = Right(Format$(Date, "yyyy"), 4) 'just take the 4 charactors from the right
'Check Year and Reset if change in calendar year
autoYear = AttachedTemplate.CustomDocumentProperties("xInvoiceYear")
If CLng(MyYear) > CLng(autoYear) Then 'convert to long for calucation
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = "0"
AttachedTemplate.CustomDocumentProperties("xInvoiceYear") = MyYear
AttachedTemplate.Save
End If
'Find the last invoice number created
AutoNumber = AttachedTemplate.CustomDocumentProperties("xInvoiceLast")
'Create a new invoice number
autoYear = AttachedTemplate.CustomDocumentProperties.Item("xInvoiceYear")
AutoNumber = AutoNumber + 1
'Save this invoice number to the template
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = AutoNumber
'Load form
frmInvoice.txtInvoice = autoYear & "_" & Format(AutoNumber, "00")
frmInvoice.Show
Glad to have helped
ASKER
AutoYear = current year
If Current Year > AutoYear then
AttachedTemplate.CustomDoc
AttachedTemplate.CustomDoc
end if
'Create a new invoice number
AutoNumber = AutoNumber + 1
'Save this invoice number to the template
AttachedTemplate.CustomDoc
'Generate Invoice Number in form
frmInvoice.txtInvoice = AutoYear & "_" & Format(AutoNumber, "00")
Would that be correct? My brain doesn't want to work tonight.