Solved

Automatic sequential number stored in word template to reset at turn of calendar year

Posted on 2010-08-16
10
980 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Fi69
  • 6
  • 4
10 Comments
 
LVL 17

Accepted Solution

by:
wobbled earned 500 total points
Comment Utility
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
 

Author Comment

by:Fi69
Comment Utility
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
 
LVL 17

Expert Comment

by:wobbled
Comment Utility
Looks pretty much it to me.  :)

0
 
LVL 17

Expert Comment

by:wobbled
Comment Utility
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
 

Author Comment

by:Fi69
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:wobbled
Comment Utility
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
 

Author Comment

by:Fi69
Comment Utility
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
 
LVL 17

Assisted Solution

by:wobbled
wobbled earned 500 total points
Comment Utility
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
 

Author Comment

by:Fi69
Comment Utility
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
 
LVL 17

Expert Comment

by:wobbled
Comment Utility
Glad to have helped
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now