Custom Number Field

[arnelgp]

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:

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

 

[o0oboneheado0o]

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:

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

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!

 

[o0oboneheado0o]

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:

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

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.

 

[Gasman]

You would hold an entry for each property, if you wanted to increment them individually?

 

[Safari]

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:

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]

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.

check and test

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

 

[o0oboneheado0o]

check and test

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

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!

 

[o0oboneheado0o]

Welcome aboard but please in the future start a new thread for a new question. Glad you got your answer.

Will Do! my apologies.