UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With Fiscal Year And Custom Number., Access 2016    
 
   
manics31
post Oct 2 2017, 02:31 PM
Post#1



Posts: 6
Joined: 18-September 17



Attached File  Copy.zip ( 1.43MB )Number of downloads: 7
Good afternoonall,
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 , YY, MM,custom number 0001-1000, so if I were to enter a new number to day it would be 18-10-0001 and so on, I'm trying to get this database to add new records using the new Fiscal year month and custom number 0001 and so on.
I have this code.
CODE
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
Go to the top of the page
 
doctor9
post Oct 2 2017, 02:34 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


manics31,

Does the same table include some sort of date field at all that would contain the month and year? If so, then you just need to store the four-digit number at the end. Then whenever you need to display this custom number in a query/form/report, you could use a simple function that concatenates the fiscal year of the given date, the month number of the given date and the four digit number.

EDIT: There are a couple of date fields, so I'm assuming that the table named "Testing" is a table of calls, and each call needs to have an "EST Number". Also, the Testing table is lacking a Primary Key to uniquely identify records. I'd recommend adding an autonumbered primary key field ("CallID") and changing the text "EST" field to "intESTNumber" with a datatype of Number-Integer. The code for adding the EST Number seems to do other stuff as well, so I'm not completely sure which parts to keep or change, but basically instead of assembling a string and assigning it to the call record, you can just look up the highest intESTNumber for the current fiscal year. Add 1 to that value, and you have the intESTNumber value for the new record. A function to display the full EST Number would be along these lines:

CODE
Public Function ESTNumber(lngCallID As Long) As String

    Dim dteCallDate As Date, intCallNumber As Integer
    
'   Get the two values from the record that determine the EST Number
    dteCallDate = DLookup("ASSG_DATE", "Testing", "CallID=" & lngCallID)
    intCallNumber = DLookup("intCallNumber", "Testing", "CallID=" & lngCallID)
    
'   First two digits - the fiscal year based on the ASSG_DATE value.
    If Month(dteCallDate) <= 9 Then
        ESTNumber = Year(dteCallDate) - 2000
    Else
        ESTNumber = Year(dteCallDate) - 2000 + 1
    End If
    
'   Next two digits - the month of the ASSG_DATE value.
    ESTNumber = ESTNumber & "-" & Format(Month(dteCallDate), "00")
    
'   Last four digits.
    ESTNumber = ESTNumber & "-" & Format(intCallNumber, "0000")

End Function

This way there's no need to deal with figuring out how many non-zero leading digits the last four digits has and such. Since users should never interact directly with the table data (only through forms, queries and reports), there's no need to worry about the data not containing the full string expression. Just calculate it on the fly whenever it's needed.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
manics31
post Oct 2 2017, 05:26 PM
Post#3



Posts: 6
Joined: 18-September 17



Thanks for your assistance Doctor9.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:56 PM