Fiscal Year Reset code

[manics31]

Good morning all,
I have an issue that hopefully someone can help me with,
I have a button on a form that assigns a number based on fiscal year , month and a number 001-1000, so if I were to enter a new number to day it would be 18-09-0001 and so on, but for some mythological reason the numbers did not reset, here is the code, can someone please tell me were the mistake is at, I can't see it. thanks a million in advance.

Private Sub btnAdd_Click()
    On Error GoTo LogErrors
    Dim MyDB As Database
    Dim myws As Workspace
    Dim rsnuest, rstesting, rsdummy As Recordset
    Dim stDocName, estnum1, tselfind As String, tselNuest, estnum As Integer
      
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
     
    Set rsnuest = MyDB.OpenRecordset("EST", DB_OPEN_DYNASET)
    Set rstesting = MyDB.OpenRecordset("Testing", DB_OPEN_DYNASET)

     estnum1 = rsnuest!ESTNumber
        
If Month(Date) <= 9 Then
    Estdt = Right(Year(Date), 2) & "-0" & Month(Date) & "-"
Else
    Estdt = Right(Year(Date), 2) & "-" & Month(Date) & "-"
End If
estnum = [estnum1] + 1

If estnum <= 9 Then
    estnew = "000" & estnum
ElseIf estnum > 9 And estnum < 100 Then
    estnew = "00" & estnum
ElseIf estnum >= 100 And estnum < 1000 Then
    estnew = "0" & estnum
ElseIf estnum >= 1000 Then
    estnew = estnum
End If
 
NuEst = Estdt & estnew

    msgcancel = "Press 'YES' to Add New Call Number & EST: "
    msgcancel = msgcancel & Me.NuEst
    msgcancel = msgcancel & ". Select 'NO' To Cancel" & Chr(13) & Chr(13)
    
    msgchoice = Dialog.Box(msgcancel, vbYesNo, "")

    Select Case msgchoice
        Case 6
        
            rsnuest.Edit
            rsnuest![ESTNumber] = estnum
            rsnuest.Update


            rstesting.AddNew
            rstesting![EST] = [NuEst]
            rstesting![ASSG_DATE] = Date
           ' rstesting![ASSIG_TO] = UCase([LastName])
            rstesting![STATUS] = "O"
           ' rstesting![Userid] = [Userid]
            rstesting.Update
   


        rsnuest.Close
        MyDB.Close
           
        stDocName = "frm_Testing"
        
        DoCmd.OpenForm stDocName, acNormal
        DoCmd.Close acForm, "frm_CallNumber", acSaveNo
    End Select
    
Exit_btnAdd_Click:
Exit Sub
LogErrors:
Call LogErrors(err.Number, err.Description, "Form:frm_CallNumber, btnAdd_Click")
Resume Exit_btnAdd_Click
End Sub

[baderms]

Try

Public Function ResetNumber()

    Dim sDtePart    As String
    Dim sSeqNbr     As String
    
    If Month(Date) > 9 Then
        sDtePart = Year(Date) + 1
    Else
        sDtePart = Year(Date)
    End If
    
    sSeqNbr = DLookup("SeqNbr", "tblSeqNbr") + 1
    sSeqNbr = LPad(sSeqNbr, "0", 4)
    
    ResetNumber = Right(sDtePart, 2) & "-" & Month(Date) & "-" & sSeqNbr
    
End Function

Private Function LPad(ByVal Str As String, ByVal strChar As String, ByVal iLen As Integer) As String

    LPad = Right$(String((iLen), strChar) & Str, iLen)
    
End Function

[manics31]

Hello, thanks for the Reply,
I could not get that to work, I ee you reference a table in your code or I'm mistaken?

Try

Public Function ResetNumber()

    Dim sDtePart    As String
    Dim sSeqNbr     As String
    
    If Month(Date) > 9 Then
        sDtePart = Year(Date) + 1
    Else
        sDtePart = Year(Date)
    End If
    
    sSeqNbr = DLookup("SeqNbr", "tblSeqNbr") + 1
    sSeqNbr = LPad(sSeqNbr, "0", 4)
    
    ResetNumber = Right(sDtePart, 2) & "-" & Month(Date) & "-" & sSeqNbr
    
End Function

Private Function LPad(ByVal Str As String, ByVal strChar As String, ByVal iLen As Integer) As String

    LPad = Right$(String((iLen), strChar) & Str, iLen)
    
End Function

[manics31]

Here is a sample of the Database. any Takers?Copy.zip

[aytee111]

If you want to start at 0001 at the beginning of every month, then you need to store the month as well as the number on the EST table. Then check to see if the month equals this month - if it does, then add 1 to the number; if it doesn't, then update the record and start from 1 again.

[manics31]

Actually I would like to start at 0001 and the beginning of each Fiscal year.

If you want to start at 0001 at the beginning of every month, then you need to store the month as well as the number on the EST table. Then check to see if the month equals this month - if it does, then add 1 to the number; if it doesn't, then update the record and start from 1 again.

[aytee111]

Then store the year on the table with the same logic.

[ssanfu]

<snip>
I have a button on a form that assigns a number based on fiscal year , month and a number 001-1000, so if I were to enter a new number to day it would be 18-09-0001 and so on, but for some mythological reason the numbers did not reset, <snip>

I didn't understand what wasn't "resetting" until aytee111 posted... (but I agree aytee111.. )


However, there are many, many other things to fix/correct in the code.
Here are a few. (this is not a slam on you. These things happen quite frequently )


The top two lines of EVERY code module should be

Option Compare Database
Option Explicit

In the IDE, click on TOOLS/OPTIONS/Editor. Check the 2nd check box "Require Variable Declaration". Any NEW modules created will have those two lines.

--------------------------------------
You have many variables that are not declared..
--------------------------------------

Lines like this:

Dim rslabs, rstest, rsdummy As Recordset

do not do what you think.
"rsdummy" is declared as a Recordset type; "rslabs" & "rstest" are declared as variants.

BAD:

    Dim MyDB As Database
    Dim myws As Workspace
    Dim tselfind, rselfind As String
    Dim rslabs, rstest, rsdummy As Recordset
    Dim estcall As String

GOOD: EACH variable type MUST be declared explicitly, like this:

    Dim MyDB As DAO.Database
    Dim myws As Workspace
    Dim tselfind As String, rselfind As String
    Dim rslabs As Recordset, rstest As Recordset, rsdummy As Recordset
    Dim estcall As String

--------------------------------------
BTW, these two lines

   Dim MyDB As DAO.Database
    Dim myws As Workspace

are effectively the same. I only use "DAO.Database".

--------------------------------------

            DoCmd.Close acForm, "frm_CallNumber", acSaveNo

The "acSaveNo" is used after design changes of the form and does not affect the saving or not saving of data (records). See Help.

--------------------------------------
You create objects and open recordsets, but don't close the recordset or destroy objects.
The rule is "If you create it, destroy it. If you open it, close it".
You have a line: "MyDB.Close". You didn't open it, so you shouldn't close it".
But you DID create it using the SET command, so you should destroy it. You do that by using "Set MyDB = Nothing".

You opened several recordsets, so you should close them. Then, you also have to destroy them:

    Set rstesting = MyDB.OpenRecordset("Testing", DB_OPEN_DYNASET)
    .... other code
    rstesting.close
    Set rstesting = nothing

===============================================
Not a problem/error, but
These lines can be replaced with one line:

'        If estnum <= 9 Then
     '       estnew = "000" & estnum
     '   ElseIf estnum > 9 And estnum < 100 Then
     '       estnew = "00" & estnum
     '   ElseIf estnum >= 100 And estnum < 1000 Then
     '       estnew = "0" & estnum
     '   ElseIf estnum >= 1000 Then
     '       estnew = estnum
     '   End If
       estnew = Right("0000" & estnum, 4)

--------------------------------------
If you are referring to a control on a form in a form class module, it is better to prefix the control name with "Me."

    Me.NuEst = Estdt & estnew

[manics31]

I appreciate all the help, the better I learn how to clean up these codes the better, thanks a million I mean it.