Autonumber for Records based on Fiscal Years

[AtlasAF]

I had a question similar to this earlier this month about autonumber fields for an Access table. What I would like to do is set a Text or Number field to do an increasing count starting with the first day of the fiscal year and ending on the last day of the fiscal. The format of the number should be CENTAF-AUAB-2005-0001. With the post I had earlier someone pointed me to a couple of FAQs and one of them does pretty close to what I need but the year needs to change at the fiscal and not the calendar year. Also I would like it to count sequentially instead of using the exact time the record was entered as the number. The FAQ is: faq181-1023. Anyone have any idea how I might get this done. I did a search through the entire forum listings for AutoNumbers and numbers based on Fiscal years to know avail. Any help would be greatly appreciated.

AtlasAF
USAF

 

[MichaelRed]

back to faq land. faq700-184 may be old but it has worded well lo htese many years. Of course, dealing with the whimsy of corporate beanners (fiscal year) will require some customizationm but then most custom routines require customization (else they would not be custom routines?).





MichaelRed

 

[AtlasAF]

Ok, a good resource for a database where more than one user will be inputing records but a little much with what I'm doing. One person will be entering the main recordset for everything and the other users will be updating those records. Here is what I came up with since my orignal post...Mind you that I can work with VBA but I'm not very savy on most of it, so the problem I'm having with this code will probably be, hopefully, an easy fix. First I made this function to get the current fiscal year:

Option Compare Database

Option Explicit
Const FMonthStart = 10   ' Numeric value representing the first month
                        ' of the fiscal year.
Const FDayStart = 1    ' Numeric value representing the first day of
                        ' the fiscal year.
Const FYearOffset = -1  ' 0 means the fiscal year starts in the
                        ' current calendar year.
                        ' -1 means the fiscal year starts in the
                        ' previous calendar year.

Function FiscalYear()
    Dim CurrentDate As Date
    
    CurrentDate = Date
    
   If CurrentDate < DateSerial(Year(CurrentDate), FMonthStart, FDayStart) Then
      FiscalYear = Year(CurrentDate) - FYearOffset - 1
   Else
      FiscalYear = Year(CurrentDate) - FYearOffset
   End If
End Function

On my forms, so this will work correctly, I changed the table cell to number instead of text and set the Input Mask with the letter prefix. Here is the BeforeUpdate event that I'm using on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ItNumber As String
    Dim Itt3215Num As Variant
        
    ItNumber = FiscalYear
    ItNumber = ItNumber & DMax("[IttFiscal]", "3215tbl") + 1
    
    Itt3215Num = [Forms]![TEST]![IttFiscal]
    
    If Itt3215Num = "" Then
        Forms![TEST]![IttFiscal] = ItNumber
    End If
End Sub

The problem, and like I said it's probably easy, is that the value the ItNumber holds isn't transfered to ItFiscal. What did I do wrong???

AtlasAF
USAF

 

[MoLaker]

Pardon me for popping in like this, but...

If Itt3215Num = "" Then
    Forms![TEST]![IttFiscal] = ItNumber
End If

Assuming you are checking to see if this is a new record, you need to check for NULL rather than zero-length. Itt3215Num is a Variant and will contain the value of the text field IttFiscal - which default is NULL for a new record unless you have set a default value of "" and set "Allow Zero Length" to True.

I think your code s/b

If IsNull(Itt3215Num) = True Then
    Forms![TEST]![IttFiscal] = ItNumber
End If

 

[MichaelRed]

I have been acused of being a bit conservative (something ~~ " ... so far to the right that Gengis couldn't see himin the line up ..."). Having admitted to this, a basic tenant would be "if it can go awry - it will. When it goes awry, it will be at the worst possible time. "single user" should be restricted to situations where the APP is opened EXCLUSIVE.

I'm sure that you won't do it. I hope it is not a decision you regret.




MichaelRed

 

[TheAceMan1]

How are ya AtlasAF . . . . .

[blue]what I need but the year needs to change at the fiscal and not the calendar year.[/blue]

What is your criteria for determining this?

[blue]Also I would like it to count sequentially instead of using the exact time the record was entered as the number.[/blue]

Could you be a little more specific . . .

See Ya! . . . . . .

 

[AtlasAF]

To respond to both of you:

MichaelRed: Thanks for the help, I'll try that and see if it works. As for the database being open exclusive to the one user, he has the only front end that allows records to be added to that table. The other users can only add records to other tables that are related off of the main table.

AceMan: The quick answer is that it is governed by a Regulation I have to follow. The number has to show the fiscal year and a sequential sequence after the fiscal respresentation. Because I won't be around this place in another three months I need this process to be as automated as possible. I'm also working on a query set and code that will transfer the previous Fiscal to another backend that is only a historical reference. Still have to figure out how I'm going to do that but if I get this problem worked out then I think I have a solution. As for the second response...in the FAQ I referenced it used the exact time down to the second to creat the number, I needed something that count sequentially not by time.

Thank you both for your help.

AtlasAF
USAF

 

[AtlasAF]

Changing the code to Null still didn't work. The value for ItNumber in the code is right but the form still won't transfer the number to the cell. Should I be using the code on another event? I've tried the Objects after update and before update, the forms afterupdate and before update and the code exicutes, I set a watch to make sure, but the value is transposed, Thanks again for the help

AtlasAF
USAF

 

[AtlasAF]

I figured out how to accomplish this one, and thought I would post it so that there was a solution on this post. In order to creat the numbers the way I needed I used the following Module and Event codes and set the Input Mask on the fields to my prefix. The prefix could be anything: "ABCD-"# (Form input mask) "ABCD-"######## (table input mask, need # for every charactor that is displayed after the text on tables)
Module

Option Compare Database

Option Explicit
Const FMonthStart = 10   ' Numeric value representing the first month
                        ' of the fiscal year.
Const FDayStart = 1    ' Numeric value representing the first day of
                        ' the fiscal year.
Const FYearOffset = -1   ' 0 means the fiscal year starts in the
                        ' current calendar year.
                        ' -1 means the fiscal year starts in the
                        ' previous calendar year.

Function FiscalYear()
    Dim CurrentDate As Date
    
    CurrentDate = Date
    
   If CurrentDate < DateSerial(Year(CurrentDate), FMonthStart, FDayStart) Then
      FiscalYear = Year(CurrentDate) - FYearOffset - 1
   Else
      FiscalYear = Year(CurrentDate) - FYearOffset
   End If
End Function

Event Code

Private Sub IttNumber_GotFocus()

  'Code creates a new IttNumber based on the current fiscal year
  'This exicutes in case the user navigates to a new request
  'using the Navigation buttons
    Dim NextIttNum As String
    Dim FiscalVerify As Double
    Dim NewIttNum As String
    
    NextIttNum = DMax("[IttNumber]", "3215tbl") + 1
    FiscalVerify = FiscalYear * 10000
    NewIttNum = FiscalYear & "0001"
    
    If IsNull(IttNumber) = True Then
        If NextIttNum - FiscalVerify > 0 Then
            IttNumber = NextIttNum
        Else
            IttNumber = NewIttNum
        End If
    End If
End Sub

Hope this might help someone someday, and thanks for all the help.

AtlasAF
USAF