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.

'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")

Open in new window

Fi69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wobbledCommented:
Why don't you store the year in the template custom properties as well.

That way you can do a comparison between the current date and the stored date, if current date is greater than stored date you need to update the stored date and set xInvoiceLast to 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fi69Author Commented:
Hmmm, so add another variable for the year
AutoYear = current year

If Current Year > AutoYear then
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = "0"
AttachedTemplate.CustomDocumentProperties("xInvoiceYear") = Current Year
end if

'Create a new invoice number
    AutoNumber = AutoNumber + 1

'Save this invoice number to the template
    AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = AutoNumber

'Generate Invoice Number in form
frmInvoice.txtInvoice = AutoYear & "_" & Format(AutoNumber, "00")


Would that be correct? My brain doesn't want to work tonight.
0
wobbledCommented:
Looks pretty much it to me.  :)

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

wobbledCommented:
Dim CurrentYear As Date

TemplateYear = AttachedTemplate.CustomDocumentProperties("xInvoiceYear").Value
CurrentYear = Year(Now)

If CurrentYear > TemplateYear then
AttachedTemplate.CustomDocumentProperties("xInvoiceLast") = "0"
AttachedTemplate.CustomDocumentProperties("xInvoiceYear") = CurrentYear
end if

That should do it (just wrote this in the reply so haven't tested this ...)
0
Fi69Author Commented:
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.
 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")

Open in new window

0
wobbledCommented:
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.CustomDocumentProperties("xInvoiceYear"))

0
Fi69Author Commented:
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?
0
wobbledCommented:
The dates that you are coming back with are the system dates for 2008 days past the start of the calendar (which is defaulted to 01/01/1900)

I don't understand why it would not be performing the formatting on the dates though.  I suppose what you could try then is to just compare them as two numbers.

If the AttachedTemplate.CustomDocumentProperties("xInvoiceYear") just holds the value 2008 you could try this

 Dim myYear As String, autoYear As String
    
   myYear = Right(Format$(Date, "yyyy"), 4)  'just take the 4 charactors from the right
   
   autoYear = AttachedTemplate.CustomDocumentProperties("xInvoiceYear")
   
     
  If CLng(myYear) > CLng(autoYear) Then    'convert to long for calucation
       MsgBox "greater"
    Else
        MsgBox "less"
    End If
   

Open in new window

0
Fi69Author Commented:
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

Open in new window

0
wobbledCommented:
Glad to have helped
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.