I'm trying to change my custom, autogenerated, Project Number that currently includes the calendar year, to fiscal year instead.
How is works now, is when the ProjectName field is entered, the AfterUpdate event fires and generates the ProjectNumber.
As an example: GP-DFM-2019-0001
The GP-DFM- never changes
The 2019 is the calendar year, that I want to change to fiscal year
The 0001 is the sequence number of projects.
I've tried many attempts but not getting anywhere.
Below is my code.
Can someone help me out?
How is works now, is when the ProjectName field is entered, the AfterUpdate event fires and generates the ProjectNumber.
As an example: GP-DFM-2019-0001
The GP-DFM- never changes
The 2019 is the calendar year, that I want to change to fiscal year
The 0001 is the sequence number of projects.
I've tried many attempts but not getting anywhere.
Below is my code.
Can someone help me out?
Code:
Private Sub ProjectName_AfterUpdate()
' This is for the auto generated ProjectNumber.
Dim Prefix As String
Dim VLast As Variant
Dim iNext As Integer
Dim MyYear As String
MyYear = Year(DateAdd("m", 3, Date))
Prefix = "GP-DFM-"
'PREVIOUS CODE: VLast = DMax("[ProjectNumber]", "[T_Project]", "[ProjectNumber] LIKE '" & Prefix & Format([txtDateCreated], "yyyy\*\'"))
VLast = DMax("[ProjectNumber]", "[T_Project]", "[ProjectNumber] LIKE '" & Prefix & MyYear'")
If IsNull(VLast) Then
iNext = 1
Else
iNext = Val(Right(VLast, 4)) + 1
End If
'PREVIOUS CODE: Me![ProjectNumber] = Prefix & Format([txtDateCreated], "yyyy") & "-" & Format(iNext, "0000")
Me![ProjectNumber] = Prefix & MyYear & "-" & Format(iNext, "0000")
End Sub