none
Create a table named "Accounting periods" based on fields on my form

    Question

  • Good morning,

    I have a form named "frmPériodesFiscales" on which I have 2 fields named "StartDate" and "EndDate"

    I also have a field name "QtyYears" on which I enter the number of years I want that new table (Accounting periods) to be created.

    Fields in Accounting periods:    ([ID], [StartDate], [EndDate]) for the rest I will use calculated field (eg: YEAR-MONTH etc...)

    So when I click the button "CREATE CALENDAR" I want the first record to contains the StartDate and EndDate entered on my form and do that for 120 periods if for example I put 10 in my "QtyYears""s field

    Here how looks like my form:

    s

    Claude from Quebec, Canada


    Claude Larocque

    Wednesday, April 19, 2017 8:50 AM

Answers

  • Claude –
    A) This was the most difficult part for me because I had not done that before. So that WAS a push.
    B) But I assume that Accounting Periods start on the first of a month? At least it should start no later than the 28th. Anyway, if it is to start in the middle of the month, we can do that too, but it will take at least one more iteration of code dev. I am using Christmas as my check date for a company whose FY begins in December...
    C) One more field easy enough.

    So after thinking it over, it seems to me a 2-part solution is more apropos: part 1 to deal with the form data, part 2 as a Class to do the calculations.

    Below, part 1 of my “solution”. Take a look at what I think of as Reasonability checks on data.

    Part 2 to follow shortly, as I clean up some details.

    I await the next Laroque wrinkle. D) perhaps?

    Option Compare Database
    Option Explicit
    
    Private d1 As Date
    Private qy As Integer
    Private glpc As GénérerlesPériodesComptables    '   google xlate
        
    Private Sub Command19_Click()
        
        If GetFormData() Then
            
            Set glpc = New GénérerlesPériodesComptables
            
            '   set properties
            glpc.StartDate = d1
            glpc.YearCount = qy
            
            glpc.Path = "D:\Users\pnr\Documents\Databases\Research\CalendarGeneration\Claude_be.accdb"
            '   change that to
            'glpc.Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
            
            glpc.Run
            
        End If
        
    End Sub
    
    Private Function GetFormData() As Boolean
        Dim m1 As Integer
        Dim y1 As Integer
       
        d1 = Me.StartDate
    '    we don't need the end date because we calculate it
        
        y1 = DatePart("yyyy", d1)
        m1 = DatePart("m", d1)
    '    we don't need the end month because we calculate it
        
        qy = Me.QtyYears
        
        '   REASONABILITY CHECKS
        '   qy > 20 ?
        '   qy < 1 ?
        '   startingYear more than 2 years ago?
        '   startingYear more than 1 year in the future?
        
        '   AND A CHANCE FOR THE USER TO CHANGE HER MIND
        '   "Your fiscal year starts in the month of MonthName(sm), right?"
        
        GetFormData = True
        
    End Function


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, April 22, 2017 4:45 PM
  • And this would be part 2. 

    Copy it into a text file with the name "GénérerlesPériodesComptables.cls", and import the class into your project.

    Let me know how it works out

    VERSION 1.0 CLASS
    BEGIN
      MultiUse = -1  'True
    END
    Attribute VB_Name = "GénérerlesPériodesComptables"
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = False
    Attribute VB_Exposed = False
    Option Compare Database
    Option Explicit
    
    Private BEdb As DAO.Database
    Private y As Integer
    Private sm As Integer   '   start month
    Private sy As Integer   '   start year
    Private startingYear As Integer
    
    Private m_tableName As String
    Private m_startdate As Date
    Private m_yearcount As Integer
    Private m_path      As String
    Private months(24)  As Integer  '   ignore months(0)
    Private periods(12) As String   '   ignore periods(0)
    
    Public Sub Run()
        Startup
        If CreateTable Then
            GenerateTheYears
            Fini
        End If
    End Sub
    
    Private Sub Startup()
        
        Dim i As Integer
        For i = 1 To 12
            months(i) = i
            months(i + 12) = i
        Next i
        
        For i = 1 To 12
            periods(i) = CStr(Format(i, "00"))
        Next i
        
        ConnectToBackEnd
    
    End Sub
    
    Private Sub ConnectToBackEnd()
        
        Set BEdb = OpenDatabase(m_path)
    
    End Sub
    
    Private Function CreateTable() As Boolean
        
        Dim result As Boolean:  result = False
            
        '   from http://allenbrowne.com/func-DAO.html#CreateTableDAO
        
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        On Error Resume Next    '   in case the table doesnt exist
        BEdb.Execute "DROP TABLE " & m_tableName & ";"  '   BE
        CurrentDb.TableDefs.Delete m_tableName          '   FE
    
        On Error GoTo errorexit
        Set tdf = BEdb.CreateTableDef(m_tableName)
        
        'Specify the fields.
        With tdf
            'AutoNumber: Long with the attribute set.
            Set fld = .CreateField("ID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            
            'Date/Time field
            Set fld = .CreateField("StartDate", dbDate)
            .Fields.Append fld
            
            'Date/Time field
            Set fld = .CreateField("EndDate", dbDate)
            .Fields.Append fld
        
            'Text field: maximum 2 characters
            Set fld = .CreateField("Period", dbText, 2)
            .Fields.Append fld
         End With
        
        'Save the table.
        BEdb.TableDefs.Append tdf
        
    '1=======================================
    '             Link the table
    '========================================
    
        Dim aLink As TableDef
        Set aLink = CurrentDb.CreateTableDef(m_tableName)
        aLink.Connect = ";DATABASE=" & m_path
        aLink.SourceTableName = m_tableName
        CurrentDb.TableDefs.Append aLink
        '   whew that was easy
        
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print m_tableName & " created."
    
        result = True
        
    exitprocessing:
        CreateTable = result
        Exit Function
    
    errorexit:
        MsgBox Error$
        Resume exitprocessing
        Resume
    End Function
    
    Private Sub GenerateTheYears()
        For y = 0 To YearCount - 1
            SetStartingDatesForYear
            GenerateAYear
        Next y
    End Sub
    
    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
    End Sub
    
    Private Sub GenerateAYear()
        Dim SQL As String
        Dim fdom As Date    '   first day of month
        Dim ldom As Date
        Dim mon As Integer
        Dim yearBreak As Boolean
        yearBreak = False
        Dim m As Integer    '   iterating over months, 1 to 12
        For m = 1 To 12
            mon = months(sm + m - 1)
            If mon < sm And Not yearBreak Then
                sy = sy + 1
                yearBreak = True
            End If
            
            fdom = DateSerial(sy, mon, 1)
            ldom = DateSerial(sy, mon + 1, 0)
            
            SQL = "INSERT INTO AccountingPeriods ( StartDate, EndDate, Period ) " _
                & "VALUES ( #" & fdom & "#, #" & ldom & "#, """ & periods(m) & """)"
            'Debug.Print SQL
            
            BEdb.Execute SQL, dbFailOnError
            
        Next m
    End Sub
    
    Private Sub Fini()
    
        BEdb.Close
        Set BEdb = Nothing
    
    End Sub
    
    
    Public Property Get Path() As Variant
        Path = m_path
    End Property
    
    Public Property Let Path(ByVal vNewValue As Variant)
        m_path = vNewValue
    End Property
    
    Public Property Get StartDate() As Variant
        StartDate = m_startdate
    End Property
    
    Public Property Let StartDate(ByVal vNewValue As Variant)
        m_startdate = vNewValue
        sm = DatePart("m", m_startdate)
        sy = DatePart("yyyy", m_startdate)
        startingYear = sy
    End Property
    
    Public Property Get YearCount() As Variant
        YearCount = m_yearcount
    End Property
    
    Public Property Let YearCount(ByVal vNewValue As Variant)
        m_yearcount = vNewValue
    End Property
    
    Public Property Get TableName() As Variant
        TableName = m_tableName
    End Property
    
    Public Property Let TableName(ByVal vNewValue As Variant)
        m_tableName = vNewValue
    End Property
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, April 22, 2017 11:21 PM
  • Claude –

    Tied to your numbers on the image:

    1.        It does not matter what day of the month is chosen, since the class calculates both the 1<sup>st</sup> and last days
    2.        The last day of the period is unnecessary, because it is redundant. When that is an opportunity for a user, they will get their input wrong. They know when it is, just don’t ask them to do it right.
    3.        Is unnecessary also. If the specified date in 1. is Jan 1, the last day will be Dec 31. Period. This also means that the FY is the same as the calendar year, and the name of the year will be that year.
    4.       
    5.        Suppose this: let’s say Debut Periode is January 1 (which is a lot easier on a user than just the numbers: you can do this in a combo box). If the date is Jan 1, do NOT enable the choice of the year name AnneeFiscaleChoisie. If the Debut Periode is any other month than January, activate AnneeFiscaleChoisie to let them choose the name of the fiscal year.
    6.        Whatever number is in NombreAnnees will determine all the end dates.

    Re the form code: I am missing the form, of course. In its place, I an using this to drive the class development. This way I don’t have to enter anything in a form, I can just RUN to a Breakpoint.

    Public Sub XGLCP()
    
            Dim glpc As GénérerlesPériodesComptables
            Set glpc = New GénérerlesPériodesComptables
            
            Dim d1 As Date:     d1 = #12/25/2017#
            Dim qy As Integer:  qy = 10
            
            '   set properties
            glpc.StartDate = d1
            glpc.YearCount = qy
            glpc.TableName = "AccountingPeriods"
            
            glpc.FYName = 2018  '   <<<<< new field in the class
            
            glpc.Path = "D:\Users\pnr\Documents\Databases\Research\CalendarGeneration\Claude_be.accdb"
            '   change that to
            'glpc.Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
            
            glpc.Run
        
    End Sub
    

    Therefore, I want the class separate from the form. To do that, I would make another property in the class to capture the FYName.

    Thus, at the top of the class

    Private m_fyName as Integer

    And somewhere later, the properties

    Public Property Get FYName() As Variant
        FYName = m_fyName
    End Property
    
    Public Property Let FYName(ByVal vNewValue As Variant)
        m_fyName = vNewValue
    End Property
    

    In this way, I can avoid "Forms![frmPériodesFiscalesPasCalendrier].[AnnéeFiscaleChoisie]" which is entirely legal, but inconvenient if I don't have the form.

    Now the FYName is an integer, so we can increment it. It should be incremented by 1 when we get ready to calculate a new year. Thus we add this code to SetStartingDatesForYear:

    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
        fiscalyear = FYName + y
    End Sub
    

    And we need to add “fiscalyear” as another integer field in the class, and output its value in the INSERT statement.

    And at this point it looks like you have too many fields in your table, which is a consequence of 4AM coding.




    peter n roth - http://PNR1.com, Maybe some useful stuff

    21 hours 6 minutes ago
  • Hi Claude,

    Not in front of a computer now to give you a link but could you do me a favor and go to my blog and look for the two articles on Cartesian query and let me know if it helps or not.

    Thanks

    Wednesday, April 19, 2017 3:06 PM
  • Hi Claude,

    In many of my applications I also work with "periods". Instead of having a Startdate and an Enddate, I use a Startdate and an Untildate. The Untildate is exactly the same as the Startdate of the next period. It is very easy to SEE and check then if there are any gaps between the periods

    When I want to select all mutations within a certain period I use the where clause:

            WHERE datefield >= Startdate AND datefield < Untildate

    This way of working guarantees that the periods always exactly match without gaps or overlaps of any point of time.

    Besides Startdate and Untildate I have (mostly) a separate field to indicate to which (fiscal) yaer the period belongs.

    With the help of the other answers to build the table, you must be able to solve this problem.

    Imb.

    Thursday, April 20, 2017 7:24 AM

All replies

  • Hi Clause,

    Have you considered using a Cartesian Query?

    Wednesday, April 19, 2017 2:38 PM
  • Thanks DBguy,

    First time I heard about that, but my access is in French, so when I research on the Internet I see that has something to do with JOIN, I use a lot of them, but do not see how to apply it here. In this question I want to create a table with financial periods based on the fields on a form...

    Can you explain how to use a Cartesian Query in creating a table?

    Thanks again

    Claude


    Claude Larocque

    Wednesday, April 19, 2017 2:50 PM
  • Hi Claude,

    Not in front of a computer now to give you a link but could you do me a favor and go to my blog and look for the two articles on Cartesian query and let me know if it helps or not.

    Thanks

    Wednesday, April 19, 2017 3:06 PM
  • Claude – thanks for an interesting exercise!

    Below is a start on some code in the module behind your form “frmPériodesFiscales”, which works only for the partial case when a fiscal year begins in January and ends in December. If the year spans two years, there is some folding that must be done in the date calculations. Also, error checks need to be expanded. And, you would probably want to export the code to a separate module/class so you could use it again somewhere else.

    Option Compare Database
    Option Explicit
    
    Private d1 As Date
    Private d2 As Date
    Private qy As Integer
    Private y As Integer
    Private sm As Integer
    Private sy As Integer
    Private startingYear As Integer
    Private em As Integer
    Private ey As Integer
    Private endingYear As Integer
    
    Private Sub Command19_Click()
        
        GetFormData
        
        CreateTable
        
        GenerateTheYears
        
    End Sub
    
    Private Sub GetFormData()
        d1 = Me.StartDate
        d2 = Me.EndDate
        qy = Me.QtyYears
        
        '   error checks omitted for simplicity
        
        '   initialize date parts
        sm = DatePart("m", d1)
        em = DatePart("m", d2)
    
        startingYear = DatePart("yyyy", d1)
        endingYear = DatePart("yyyy", d2)
        
    End Sub
    
    Private Sub CreateTable()
        
        Debug.Print "Creating Table"
        On Error Resume Next    '   in case the table doesnt exist
        CurrentDb.Execute "DROP TABLE AccountingPeriods;"
        Application.RefreshDatabaseWindow
        
        '   from http://allenbrowne.com/func-DAO.html#CreateTableDAO
        
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        'Initialize the AccountingPeriods table.
        Set db = CurrentDb()
        Set tdf = db.CreateTableDef("AccountingPeriods")
        
        'Specify the fields.
        With tdf
            'AutoNumber: Long with the attribute set.
            Set fld = .CreateField("ID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            
            'Date/Time field
            Set fld = .CreateField("StartDate", dbDate)
            .Fields.Append fld
            
            'Date/Time field
            Set fld = .CreateField("EndDate", dbDate)
            .Fields.Append fld
        End With
        
        'Save the table.
        db.TableDefs.Append tdf
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print "AccountingPeriods created."
        
        Application.RefreshDatabaseWindow
    End Sub
    
    Private Sub GenerateTheYears()
        For y = 0 To qy - 1
            SetStartingDatesForYear
            GenerateAYear
        Next y
    End Sub
    
    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
        ey = endingYear + y
    End Sub
    
    Private Sub GenerateAYear()
        '   assume sy and ey are the same year; that is,
        '   ignore the possibility that sy and ey are different years
        Dim SQL As String
        Dim fdom As Date    '   first day of month
        Dim ldom As Date
        Dim m As Integer    '   iterating over months, 1 to 12
        For m = sm To em
            
            fdom = DateSerial(sy, m, 1)
            ldom = DateSerial(ey, m + 1, 0)
            
            SQL = "INSERT INTO AccountingPeriods ( StartDate, EndDate ) " _
                & "VALUES ( #" & fdom & "#, #" & ldom & "#)"
            'Debug.Print SQL
            
            CurrentDb.Execute SQL, dbFailOnError
            
        Next m
    End Sub
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, April 20, 2017 4:51 AM
  • Hi Claude,

    In many of my applications I also work with "periods". Instead of having a Startdate and an Enddate, I use a Startdate and an Untildate. The Untildate is exactly the same as the Startdate of the next period. It is very easy to SEE and check then if there are any gaps between the periods

    When I want to select all mutations within a certain period I use the where clause:

            WHERE datefield >= Startdate AND datefield < Untildate

    This way of working guarantees that the periods always exactly match without gaps or overlaps of any point of time.

    Besides Startdate and Untildate I have (mostly) a separate field to indicate to which (fiscal) yaer the period belongs.

    With the help of the other answers to build the table, you must be able to solve this problem.

    Imb.

    Thursday, April 20, 2017 7:24 AM
  • Hi Peter,

    The only word that comes to mind is WOW... works at first try, thanks so much for taking the time to give me a complete solution, this is what I call BIG HELP...

    A) Now, to push it a little, the table is created in the current database that is a front end :) the back-end name is "Auto-Caisse 2017 - BE.accdb" and the location is: "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours" is it possible then to have the table created in the back-end and automatically create a link in the front-end?

    Note: The front end name is "Auto-Caisse 2017 - Commerce de détails.accdb" in the same directory...

    B) Because they are accounting periods, many businesses start them at different month, I feel that I am taking advantage of you here, but, if you could that would be great! So it could span over two years :)

    C) I will like to have another field in the table "AccountingPeriods" name PeriodID that will begin with 01 and end with 12 for each year, this field not an autonumber but a text field...

    Thanks again for your time

    Claude


    Claude Larocque



    Thursday, April 20, 2017 9:54 AM
  • Thanks Imb, with the code of Peter is works perfectly, however, I will use your advice and see what goes on.

    Claude


    Claude Larocque

    Thursday, April 20, 2017 9:55 AM
  • Ha ha!

    You realize I don’t have the French keyboard, and that I removed the space from the table [Accounting periods] so I would not have to use [ and ].

    Let me find a few moments, if someone else doesn’t jump in.

    In the meantime, write the error checks on the user input and let’s see what you can do yourself.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, April 20, 2017 2:55 PM
  • Hi Peter,

    I program with Access since 2008 and I have created a point of sale system and currently have 207 customers, since the beginning I have use this site to receive the help I needed. People like yourself, Andrey, the dbGuy, Imb, and many more are the reasons why I was able to achieve this software, so I have already put errors checks for that form, usually I am using this":

      On Error GoTo Error_Handler
    
    
    MY CODE HERE....
    
    Error_Handler_Exit:
        On Error Resume Next
    Exit Sub
    
    Error_Handler:
        MsgBox "L'erreur suivante est survenue, prenez le no d'erreur en note ET CONTACTEZ LE SUPPORT D'AUTO-CAISSE!" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: frmDétails commande-Accepter le dépôt" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "Une erreur est survenue, contactez (514) 767-5440!"
        Resume Error_Handler_Exit
    End Sub

    I put the name of the form in the Error Source section...

    Fiscal years could be on a two years span as you surely know, my original answer was answered perfectly by you, the reason I didn't put "Mark as answer" yet is to see if someone else doesn't jump in like you wrote :)

    Claude


    Claude Larocque

    Friday, April 21, 2017 2:42 PM
  • Fiscal years could be on a two years span as you surely know, my original answer was answered perfectly by you, the reason I didn't put "Mark as answer" yet is to see if someone else doesn't jump in like you wrote :)

    Hi Claude,

    As I mentioned already in my previous answer, I use a separate field in the Period_tbl to indicate to which fiscal or business year the period belongs.

    You can also add a next field to that table with the PeriodID 01 through 12. I hardly use such an PeriodID field, because sorting can be done on fiscal year and Startdate, and the Period-indication can be extracted from the Month of that period.

    Imb.

    Edit: the fiscal year can span different calender years.

    • Edited by Imb-hb Friday, April 21, 2017 3:33 PM Edit
    Friday, April 21, 2017 3:31 PM
  • Claude –
    A) This was the most difficult part for me because I had not done that before. So that WAS a push.
    B) But I assume that Accounting Periods start on the first of a month? At least it should start no later than the 28th. Anyway, if it is to start in the middle of the month, we can do that too, but it will take at least one more iteration of code dev. I am using Christmas as my check date for a company whose FY begins in December...
    C) One more field easy enough.

    So after thinking it over, it seems to me a 2-part solution is more apropos: part 1 to deal with the form data, part 2 as a Class to do the calculations.

    Below, part 1 of my “solution”. Take a look at what I think of as Reasonability checks on data.

    Part 2 to follow shortly, as I clean up some details.

    I await the next Laroque wrinkle. D) perhaps?

    Option Compare Database
    Option Explicit
    
    Private d1 As Date
    Private qy As Integer
    Private glpc As GénérerlesPériodesComptables    '   google xlate
        
    Private Sub Command19_Click()
        
        If GetFormData() Then
            
            Set glpc = New GénérerlesPériodesComptables
            
            '   set properties
            glpc.StartDate = d1
            glpc.YearCount = qy
            
            glpc.Path = "D:\Users\pnr\Documents\Databases\Research\CalendarGeneration\Claude_be.accdb"
            '   change that to
            'glpc.Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
            
            glpc.Run
            
        End If
        
    End Sub
    
    Private Function GetFormData() As Boolean
        Dim m1 As Integer
        Dim y1 As Integer
       
        d1 = Me.StartDate
    '    we don't need the end date because we calculate it
        
        y1 = DatePart("yyyy", d1)
        m1 = DatePart("m", d1)
    '    we don't need the end month because we calculate it
        
        qy = Me.QtyYears
        
        '   REASONABILITY CHECKS
        '   qy > 20 ?
        '   qy < 1 ?
        '   startingYear more than 2 years ago?
        '   startingYear more than 1 year in the future?
        
        '   AND A CHANCE FOR THE USER TO CHANGE HER MIND
        '   "Your fiscal year starts in the month of MonthName(sm), right?"
        
        GetFormData = True
        
    End Function


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, April 22, 2017 4:45 PM
  • And this would be part 2. 

    Copy it into a text file with the name "GénérerlesPériodesComptables.cls", and import the class into your project.

    Let me know how it works out

    VERSION 1.0 CLASS
    BEGIN
      MultiUse = -1  'True
    END
    Attribute VB_Name = "GénérerlesPériodesComptables"
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = False
    Attribute VB_Exposed = False
    Option Compare Database
    Option Explicit
    
    Private BEdb As DAO.Database
    Private y As Integer
    Private sm As Integer   '   start month
    Private sy As Integer   '   start year
    Private startingYear As Integer
    
    Private m_tableName As String
    Private m_startdate As Date
    Private m_yearcount As Integer
    Private m_path      As String
    Private months(24)  As Integer  '   ignore months(0)
    Private periods(12) As String   '   ignore periods(0)
    
    Public Sub Run()
        Startup
        If CreateTable Then
            GenerateTheYears
            Fini
        End If
    End Sub
    
    Private Sub Startup()
        
        Dim i As Integer
        For i = 1 To 12
            months(i) = i
            months(i + 12) = i
        Next i
        
        For i = 1 To 12
            periods(i) = CStr(Format(i, "00"))
        Next i
        
        ConnectToBackEnd
    
    End Sub
    
    Private Sub ConnectToBackEnd()
        
        Set BEdb = OpenDatabase(m_path)
    
    End Sub
    
    Private Function CreateTable() As Boolean
        
        Dim result As Boolean:  result = False
            
        '   from http://allenbrowne.com/func-DAO.html#CreateTableDAO
        
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        
        On Error Resume Next    '   in case the table doesnt exist
        BEdb.Execute "DROP TABLE " & m_tableName & ";"  '   BE
        CurrentDb.TableDefs.Delete m_tableName          '   FE
    
        On Error GoTo errorexit
        Set tdf = BEdb.CreateTableDef(m_tableName)
        
        'Specify the fields.
        With tdf
            'AutoNumber: Long with the attribute set.
            Set fld = .CreateField("ID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            
            'Date/Time field
            Set fld = .CreateField("StartDate", dbDate)
            .Fields.Append fld
            
            'Date/Time field
            Set fld = .CreateField("EndDate", dbDate)
            .Fields.Append fld
        
            'Text field: maximum 2 characters
            Set fld = .CreateField("Period", dbText, 2)
            .Fields.Append fld
         End With
        
        'Save the table.
        BEdb.TableDefs.Append tdf
        
    '1=======================================
    '             Link the table
    '========================================
    
        Dim aLink As TableDef
        Set aLink = CurrentDb.CreateTableDef(m_tableName)
        aLink.Connect = ";DATABASE=" & m_path
        aLink.SourceTableName = m_tableName
        CurrentDb.TableDefs.Append aLink
        '   whew that was easy
        
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print m_tableName & " created."
    
        result = True
        
    exitprocessing:
        CreateTable = result
        Exit Function
    
    errorexit:
        MsgBox Error$
        Resume exitprocessing
        Resume
    End Function
    
    Private Sub GenerateTheYears()
        For y = 0 To YearCount - 1
            SetStartingDatesForYear
            GenerateAYear
        Next y
    End Sub
    
    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
    End Sub
    
    Private Sub GenerateAYear()
        Dim SQL As String
        Dim fdom As Date    '   first day of month
        Dim ldom As Date
        Dim mon As Integer
        Dim yearBreak As Boolean
        yearBreak = False
        Dim m As Integer    '   iterating over months, 1 to 12
        For m = 1 To 12
            mon = months(sm + m - 1)
            If mon < sm And Not yearBreak Then
                sy = sy + 1
                yearBreak = True
            End If
            
            fdom = DateSerial(sy, mon, 1)
            ldom = DateSerial(sy, mon + 1, 0)
            
            SQL = "INSERT INTO AccountingPeriods ( StartDate, EndDate, Period ) " _
                & "VALUES ( #" & fdom & "#, #" & ldom & "#, """ & periods(m) & """)"
            'Debug.Print SQL
            
            BEdb.Execute SQL, dbFailOnError
            
        Next m
    End Sub
    
    Private Sub Fini()
    
        BEdb.Close
        Set BEdb = Nothing
    
    End Sub
    
    
    Public Property Get Path() As Variant
        Path = m_path
    End Property
    
    Public Property Let Path(ByVal vNewValue As Variant)
        m_path = vNewValue
    End Property
    
    Public Property Get StartDate() As Variant
        StartDate = m_startdate
    End Property
    
    Public Property Let StartDate(ByVal vNewValue As Variant)
        m_startdate = vNewValue
        sm = DatePart("m", m_startdate)
        sy = DatePart("yyyy", m_startdate)
        startingYear = sy
    End Property
    
    Public Property Get YearCount() As Variant
        YearCount = m_yearcount
    End Property
    
    Public Property Let YearCount(ByVal vNewValue As Variant)
        m_yearcount = vNewValue
    End Property
    
    Public Property Get TableName() As Variant
        TableName = m_tableName
    End Property
    
    Public Property Let TableName(ByVal vNewValue As Variant)
        m_tableName = vNewValue
    End Property
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, April 22, 2017 11:21 PM
  • Thanks so much Peter, it works after a few changes, but this is amazing, now, the customer can choose a form if the fiscal year is a calendar year, if its not, then they choose not a calendar year and another form opens and it works great..

    Thanks again.

    Claude


    Claude Larocque

    Monday, April 24, 2017 12:55 AM
  • You're welcome!

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, April 24, 2017 1:51 AM
  • Hi Peter, didn't know if I should start a new discussion, but I take a chance, if you do not see it, I will create a new one.

    Like I mentioned previously, it works great, but like all developers I try to improve the program. I have added some fields but the one I am interested in is called "AnnéeFiscale", when this is a calendar year, there is no problem, however, when for example the fiscal year begins on 2017-07-01 and end on 2018-06-30 then the first fiscal year is choose by the user, because some company will say that with the dates mentioned above, this is the 2017 while other will say this is the 2018 fiscal year.

    So my question is this, at the moment, my column AnnéeFiscale follow the year of the period ending, let says that the customer chooses 2018 for the starting year, then, each time the period 01 is encountered then, the fiscal year should be (AnnéeFiscaleChoisie) mentioned in the form +1

    Make sense?

    I did change the code a lot from your previous response, I also attach an image of the user form to generate a fiscal year calendar...

    An image worth a thousand words and below it I put the code of the form and the code of the class module that you give me.

    Here is the code on the form and the class module..

    Option Compare Database
    Option Explicit
    
    Private d1 As Date
    Private qy As Integer
    Private glpc As GénérerlesPériodesComptables    '   google xlate
        
    Private Sub CréerCalendrierFiscal_Click()
      On Error GoTo Error_Handler
      
    Dim strMessageHeader As String
    Dim strMessageChoisirAnnée As String
    Dim strMessageNombreAnnées As String
    Dim strMessageCalendrierCréé As String
    Dim strMessageCochéPremièreAnnée As String
    
    Select Case Me.Langue.Caption
        Case "FR"
            strMessageHeader = "Logiciel Auto-Caisse, Inc. MESSAGE!"
            strMessageNombreAnnées = "Vous devez choisir pour combien d'années le calendrier doit être créé. Nous vous suggérons une période de 10 ans! (Entrer le chiffre 10)!"
            strMessageCalendrierCréé = "Votre calendrier a été créé selon vos spécifications!"
            strMessageChoisirAnnée = "Vous devez inscrire l'année fiscale de la première période, une année s'ajoutera à chaque première période créer!"
            strMessageCochéPremièreAnnée = "Vous devez choisir si l'année fiscale est l'année de début ou de fin de période!"
        Case "EN"
            strMessageHeader = "Auto-Caisse Software, Inc. MESSAGE!"
            strMessageNombreAnnées = "You must choose for how many years the calendar must be created. We suggest a period of 10 years! (Enter the number 10)"
            strMessageCalendrierCréé = "Your calendar has been created according to your specifications!"
            strMessageChoisirAnnée = "You must enter the fiscal year of the first period, one year will be added to each first period created!"
            strMessageCochéPremièreAnnée = "You must choose whether the fiscal year is the start or end year of the period!"
    End Select
    
    If IsNull(Me.NombreAnnées) Then
    Call MsgBox(strMessageNombreAnnées, vbOKOnly, strMessageHeader)
    DoCmd.GoToControl "NombreAnnées"
    Exit Sub
    Else
    End If
    If IsNull(Me.AnnéeFiscaleChoisie) Then
    Call MsgBox(strMessageChoisirAnnée, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    If Me.TotalCoché = 0 Then
    Call MsgBox(strMessageCochéPremièreAnnée, vbOKOnly, strMessageHeader)
    Exit Sub
    Else
    End If
    
        If GetFormData() Then
            
            Set glpc = New GénérerlesPériodesComptables
            
            '   set properties
            glpc.StartDate = d1
            glpc.YearCount = qy
            
            'glpc.Path = "D:\Users\pnr\Documents\Databases\Research\CalendarGeneration\Claude_be.accdb"
            '   change that to
            glpc.Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
            
            glpc.Run
            
        End If
        
    Call MsgBox(strMessageCalendrierCréé, vbOKOnly, strMessageHeader)
    DoCmd.Close acForm, Me.Name
    
    Error_Handler_Exit:
        On Error Resume Next
    Exit Sub
    
    Error_Handler:
        MsgBox "L'erreur suivante est survenue, prenez le no d'erreur en note ET CONTACTEZ LE SUPPORT D'AUTO-CAISSE!" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: frmPériodesFiscalePasCalendrier - CréerCalendrierFiscal" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "Une erreur est survenue, contactez (514) 767-5440!"
        Resume Error_Handler_Exit
        
    End Sub
    
    Private Function GetFormData() As Boolean
        Dim m1 As Integer
        Dim y1 As Integer
       
        d1 = Me.DébutPériode
    '    we don't need the end date because we calculate it
        
        y1 = DatePart("yyyy", d1)
        m1 = DatePart("m", d1)
    '    we don't need the end month because we calculate it
        
        qy = Me.NombreAnnées
        
        '   REASONABILITY CHECKS
        '   qy = 10
        '   qy < 1 ?
        '   startingYear more than 2 years ago?
        '   startingYear more than 1 year in the future?
        
        '   AND A CHANCE FOR THE USER TO CHANGE HER MIND
        '   MsgBoxYesNo = "Your fiscal year starts in the month of MonthName(sm), right?"
        
        GetFormData = True
        
    End Function
    
    
    Private Sub Form_Load()
    formtran Me
    Me.FYDébut = False
    Me.FYFin = False
    Me.DébutPériode = ""
    Me.FinPériode = ""
    End Sub
    
    Private Sub FYDébut_Click()
    Me.FYFin = False
    Me.AnnéeFiscaleChoisie = DatePart("yyyy", DébutPériode)
    End Sub
    
    Private Sub FYFin_Click()
    Me.FYDébut = False
    Me.AnnéeFiscaleChoisie = DatePart("yyyy", FinPériode)
    End Sub
    
    Private Sub NombreAnnées_GotFocus()
    Me.NombreAnnées.BackColor = "65535"
    End Sub
    

    And the class module:

    Option Compare Database
    Option Explicit
    
    Private BEdb As DAO.Database
    Private y As Integer
    Private sm As Integer   '   start month
    Private sy As Integer   '   start year
    Private startingYear As Integer
    Private m_tableName As String
    Private m_startdate As Date
    Private m_yearcount As Integer
    Private m_path      As String
    Private months(24)  As Integer  '   ignore months(0)
    Private periods(12) As String   '   ignore periods(0)
    Private années(12) As String
    
    
    Public Sub Run()
        Startup
        If CreateTable Then
            GenerateTheYears
            Fini
        End If
    End Sub
    
    Private Sub Startup()
        Dim f As Integer
        Dim i As Integer
        
        For i = 1 To 12
            months(i) = i
            months(i + 12) = i
        Next i
        
        For i = 1 To 12
            periods(i) = CStr(Format(i, "00"))
        Next i
        
        For f = 1 To 12
            années(f) = Forms![frmPériodesFiscalesPasCalendrier].[AnnéeFiscaleChoisie]
        Next f
       
        ConnectToBackEnd
    
    End Sub
    
    Private Sub ConnectToBackEnd()
       m_path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
        Set BEdb = OpenDatabase(m_path)
    
    End Sub
    
    Private Function CreateTable() As Boolean
        
        Dim Result As Boolean:  Result = False
        ConnectToBackEnd
        '   from http://allenbrowne.com/func-DAO.html#CreateTableDAO
        
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim db As DAO.Database
            Set db = CurrentDb()
    
        'On Error Resume Next    '   in case the table doesnt exist
        BEdb.Execute "DROP TABLE [Périodes Fiscales];"  '   BE
        CurrentDb.TableDefs.Delete "Périodes fiscales"          '   FE
    
        On Error GoTo errorexit
        Set tdf = BEdb.CreateTableDef("Périodes fiscales")
        
        'Specify the fields.
        With tdf
            'AutoNumber: Long with the attribute set.
            Set fld = .CreateField("ID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            
            
            'Date/Time field
            Set fld = .CreateField("DébutPériode", dbDate)
            .Fields.Append fld
            
            'Date/Time field
            Set fld = .CreateField("FInPériode", dbDate)
            .Fields.Append fld
            
            'Text field: maximum 2 characters
            Set fld = .CreateField("NoPériode", dbText, 2)
            .Fields.Append fld
            
            Set fld = .CreateField("Sélectionner", dbBoolean)
            .Fields.Append fld
            
            Set fld = .CreateField("AnnéeCalendrier", dbText, 4)
            .Fields.Append fld
            
            Set fld = .CreateField("AnnéeFiscale", dbText, 4)
            .Fields.Append fld
            
            Set fld = .CreateField("PériodeFiscale", dbText, 7)
            .Fields.Append fld
            
            'Dim prp As DAO.Property
            'Set prp = fld.CreateProperty( _
            '"Sélectionner", dbInteger, acCheckBox)
            ' append the property to the field
            'fld.Properties.Append prp
    
        End With
        
        'Save the table.
        BEdb.TableDefs.Append tdf
    
        
    '1=======================================
    '             Link the table
    '========================================
    
        Dim aLink As TableDef
        Set aLink = CurrentDb.CreateTableDef("Périodes fiscales")
        aLink.Connect = ";DATABASE=" & m_path
        aLink.SourceTableName = "Périodes fiscales"
        CurrentDb.TableDefs.Append aLink
        '   whew that was easy
        
        Set fld = Nothing
        Set tdf = Nothing
        Debug.Print "Périodes fiscales" & " Les périodes comptables ont été créées."
    
        Result = True
        
    exitprocessing:
        CreateTable = Result
        Exit Function
    
    errorexit:
        MsgBox Error$
        Resume exitprocessing
        Resume
    End Function
    
    Private Sub GenerateTheYears()
        For y = 0 To YearCount - 1
            SetStartingDatesForYear
            GenerateAYear
        Next y
    End Sub
    
    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
    End Sub
    
    Private Sub GenerateAYear()
        Dim SQL As String
        Dim fdom As Date    '   first day of month
        Dim ldom As Date
        Dim mon As Integer
        Dim yearBreak As Boolean
        yearBreak = False
        Dim m As Integer    '   iterating over months, 1 to 12
        Dim calcF1 As String
        Dim calcF2 As String
        
        For m = 1 To 12
            mon = months(sm + m - 1)
            If mon < sm And Not yearBreak Then
                sy = sy + 1
                yearBreak = True
            End If
            
            fdom = DateSerial(sy, mon, 1)
            ldom = DateSerial(sy, mon + 1, 0)
            calcF1 = DatePart("yyyy", ldom)
                    
            SQL = "INSERT INTO [Périodes fiscales] ( DébutPériode, FinPériode, NoPériode, AnnéeCalendrier, AnnéeFiscale, PériodeFiscale  ) " _
                & "VALUES ( #" & fdom & "#, #" & ldom & "#, """ & periods(m) & """, """ & calcF1 & """,  """ & années(m) & """, """ & periods(m) & "-" & années(m) & """ )"
            'Debug.Print SQL
            
            BEdb.Execute SQL, dbFailOnError
            
        Next m
    End Sub
    
    Private Sub Fini()
    
        BEdb.Close
        Set BEdb = Nothing
    
    End Sub
    
    Public Property Get Path() As Variant
        Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
    End Property
    
    Public Property Let Path(ByVal vNewValue As Variant)
        m_path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
    End Property
    
    Public Property Get StartDate() As Variant
        StartDate = Forms![frmPériodesFiscalesPasCalendrier].[DébutPériode]
    End Property
    
    Public Property Let StartDate(ByVal vNewValue As Variant)
        m_startdate = Forms![frmPériodesFiscalesPasCalendrier].[DébutPériode]
        sm = DatePart("m", m_startdate)
        sy = DatePart("yyyy", m_startdate)
        startingYear = sy
    End Property
    
    Public Property Get YearCount() As Variant
        YearCount = Forms![frmPériodesFiscalesPasCalendrier].[NombreAnnées]
    End Property
    
    Public Property Let YearCount(ByVal vNewValue As Variant)
        m_yearcount = Forms![frmPériodesFiscalesPasCalendrier].[NombreAnnées]
    End Property
    
    Public Property Get TableName() As Variant
        TableName = m_tableName
    End Property
    
    Public Property Let TableName(ByVal vNewValue As Variant)
        m_tableName = "Périodes Fiscales"
    End Property
    

    I know this is not too big of a job, but at 4:00 AM...it is :)

    Thanks

    Claude

    Note: if someone knows how, please jump in, I need to install a new customer on Friday and a lot of test to do..


    Claude Larocque

    Tuesday, April 25, 2017 8:06 AM
  • Hi Claude - I have seen. Gimme some time to absorb.

    4AM requires certain chemicals ...


    peter n roth - http://PNR1.com, Maybe some useful stuff

    22 hours 17 minutes ago
  • Claude –

    Tied to your numbers on the image:

    1.        It does not matter what day of the month is chosen, since the class calculates both the 1<sup>st</sup> and last days
    2.        The last day of the period is unnecessary, because it is redundant. When that is an opportunity for a user, they will get their input wrong. They know when it is, just don’t ask them to do it right.
    3.        Is unnecessary also. If the specified date in 1. is Jan 1, the last day will be Dec 31. Period. This also means that the FY is the same as the calendar year, and the name of the year will be that year.
    4.       
    5.        Suppose this: let’s say Debut Periode is January 1 (which is a lot easier on a user than just the numbers: you can do this in a combo box). If the date is Jan 1, do NOT enable the choice of the year name AnneeFiscaleChoisie. If the Debut Periode is any other month than January, activate AnneeFiscaleChoisie to let them choose the name of the fiscal year.
    6.        Whatever number is in NombreAnnees will determine all the end dates.

    Re the form code: I am missing the form, of course. In its place, I an using this to drive the class development. This way I don’t have to enter anything in a form, I can just RUN to a Breakpoint.

    Public Sub XGLCP()
    
            Dim glpc As GénérerlesPériodesComptables
            Set glpc = New GénérerlesPériodesComptables
            
            Dim d1 As Date:     d1 = #12/25/2017#
            Dim qy As Integer:  qy = 10
            
            '   set properties
            glpc.StartDate = d1
            glpc.YearCount = qy
            glpc.TableName = "AccountingPeriods"
            
            glpc.FYName = 2018  '   <<<<< new field in the class
            
            glpc.Path = "D:\Users\pnr\Documents\Databases\Research\CalendarGeneration\Claude_be.accdb"
            '   change that to
            'glpc.Path = "C:\Auto-Caisse\Clients\Auto-Caisse Database en cours\Auto-Caisse 2017 - BE.accdb"
            
            glpc.Run
        
    End Sub
    

    Therefore, I want the class separate from the form. To do that, I would make another property in the class to capture the FYName.

    Thus, at the top of the class

    Private m_fyName as Integer

    And somewhere later, the properties

    Public Property Get FYName() As Variant
        FYName = m_fyName
    End Property
    
    Public Property Let FYName(ByVal vNewValue As Variant)
        m_fyName = vNewValue
    End Property
    

    In this way, I can avoid "Forms![frmPériodesFiscalesPasCalendrier].[AnnéeFiscaleChoisie]" which is entirely legal, but inconvenient if I don't have the form.

    Now the FYName is an integer, so we can increment it. It should be incremented by 1 when we get ready to calculate a new year. Thus we add this code to SetStartingDatesForYear:

    Private Sub SetStartingDatesForYear()
        sy = startingYear + y
        fiscalyear = FYName + y
    End Sub
    

    And we need to add “fiscalyear” as another integer field in the class, and output its value in the INSERT statement.

    And at this point it looks like you have too many fields in your table, which is a consequence of 4AM coding.




    peter n roth - http://PNR1.com, Maybe some useful stuff

    21 hours 6 minutes ago
  • Perfect, now everything works perfectly, I can't thank you enough, this is what I call BIG HELP

    Claude


    Claude Larocque

    4 hours 39 minutes ago
  • Hi Claude,

    Good luck with your project!

    3 hours 3 minutes ago
  • Get some sleep!

    peter n roth - http://PNR1.com, Maybe some useful stuff

    2 hours 10 minutes ago