Help with Incrementing a Custom Formatted Unique Number
I have a database with a table "tblCAPARMain" in which there are numerous fields some of which are:
BaseNum
FormatNum
CAPAR#
CAPARDate
I also have a form "frmCAPARMain" in which I want to create a unique number for each record to be stored in the "CAPAR#" field in "tblCAPARMain". The format of the number is ”C09-001”, where the “C” is a constant, the “09” is the last two digits of the current year when the record was entered, and the “001” is a unique consecutive number.
I am using the following code to generate the number “After Update” of a combo box.
If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & [CAPARDate] & "-" & Format([BaseNum], "000")
Me.Refresh
Where "CAPARDate" has the "Default Value" in "tblCAPARMain" set to “=Right(Date(),4)”
With this code the unique number increments correctly, and everything works fine except that I get the four digit year. i.e. "C2009-001". If I change the "CAPARDate" "Default Value" to “=Right(Date(),2)”, then the format comes out right as "C09-001", but the unique number will not increment.
I have also tried the following code with the "CAPARDate" default value set as“=Right(Date(),4)”
If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & Format([CAPARDate], “yy”) & "-" & Format([BaseNum], "000")
Me.Refresh
This returns “C05-001” no matter what the current year is set to, but does increment.
I also tried the above with the "CAPARDate" default value set as “=Right(Date(),2)”, this returns “C00-001” for all years and does not increment.
Can anyone help me get the correct format “C09-001” and have it increment correctly?
Thank you in advance for your help.
Paul
I have a database with a table "tblCAPARMain" in which there are numerous fields some of which are:
BaseNum
FormatNum
CAPAR#
CAPARDate
I also have a form "frmCAPARMain" in which I want to create a unique number for each record to be stored in the "CAPAR#" field in "tblCAPARMain". The format of the number is ”C09-001”, where the “C” is a constant, the “09” is the last two digits of the current year when the record was entered, and the “001” is a unique consecutive number.
I am using the following code to generate the number “After Update” of a combo box.
If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & [CAPARDate] & "-" & Format([BaseNum], "000")
Me.Refresh
Where "CAPARDate" has the "Default Value" in "tblCAPARMain" set to “=Right(Date(),4)”
With this code the unique number increments correctly, and everything works fine except that I get the four digit year. i.e. "C2009-001". If I change the "CAPARDate" "Default Value" to “=Right(Date(),2)”, then the format comes out right as "C09-001", but the unique number will not increment.
I have also tried the following code with the "CAPARDate" default value set as“=Right(Date(),4)”
If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & Format([CAPARDate], “yy”) & "-" & Format([BaseNum], "000")
Me.Refresh
This returns “C05-001” no matter what the current year is set to, but does increment.
I also tried the above with the "CAPARDate" default value set as “=Right(Date(),2)”, this returns “C00-001” for all years and does not increment.
Can anyone help me get the correct format “C09-001” and have it increment correctly?
Thank you in advance for your help.
Paul
Last edited: