Custom Number Field

create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
    
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
    
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
    
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...

You sir are a god! Thank you so much!!! I was so close to giving up on this and not only does it work great, I was able to kind of get more clarity on the structure behind the scenes. Thank you so much!
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...

I do have one last question: Being that we have multiple properties, what is the probability of having the number go in sequence relative to the property code that is chosen? Example: STC-0001-21, STC-0002-21, etc from one property, yet CC-0001-21, CC-0002-21, etc... from the other property. In trying to think it through, I was tinkering with "If" statements and yeah, I have no clue.
 
You would hold an entry for each property, if you wanted to increment them individually?
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...
 
o0oboneheado0o said:
I do have one last question: Being that we have multiple properties, what is the probability of having the number go in sequence relative to the property code that is chosen? Example: STC-0001-21, STC-0002-21, etc from one property, yet CC-0001-21, CC-0002-21, etc... from the other property. In trying to think it through, I was tinkering with "If" statements and yeah, I have no clue.
Click to expand...
check and test
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
    
    Dim strValue As String
    Dim ln As Long
    
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
    
    ln = Len(propCode)
    
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", _
                    "Left$([" & SEQN_FIELD & "], " & ln & ") = '" & propCode & "' AND " & _
                    "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
    
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
 
arnelgp said:
check and test
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    Dim ln As Long
   
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    ln = Len(propCode)
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", _
                    "Left$([" & SEQN_FIELD & "], " & ln & ") = '" & propCode & "' AND " & _
                    "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...
So sorry for the late response on this but yes this works beautifully and as desired... Things have been really busy and haven't had enough time in the day to keep up on everything. I appreciate this help so very much!
 
You must log in or register to reply here.

Similar threads

D
Darshan Hiranandani : Can anyone suggest how to format ascending numbers for varying input types?
Replies
2
Views
696
GPGeorge
GPGeorge
E
Emmanuel Katto : Best Way to Format Ascending Lot Numbers Based on Ingredient and Date
Replies
6
Views
777
Mark_
M
asteropi
Solved How to format ascending numbers for different inputs?
2
Replies
39
Views
2,915
asteropi
asteropi
S
End of the Month
Replies
4
Views
674
Sebsboutique
S
Gasman
Solved Query advice please
Replies
7
Views
403
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom