Automatic numbering with some criterias

[guestbb]

So i have a form where for every new entry of shipments you need to add a IDnumber to the shipment. The IDnumber is made of year and 4 digit increasing number. Here is the example: 15-0025 (the number 15 is the current year 2015, and the 0025 is the number that increases everytime, it starts from 0001).
Now I want it to check what last IDnumber of the shipment is and increase it or change it entirely if needed (I will explain this now).

Examples:
-I want it to increase the 4 digit number if the year from the previous IDnumber matches current year. So if last IDnumber was 15-0333, I want the next one to be 15-0334, increase by one.
-If the year has changed, example now is 2016. I want it to change the entire ID from example 15-2556 to 16-0001. Like a fresh start for a new year.

I have done some code for this but some things I just didn't know how to do.
First: I dont know how to get from the last IDnumber the year part of the IDnumber (those first two numbers) to compare it with the current year.

Second: It works when I put example 14-2545 for the last IDnumber and it changes it to 15-0001. But it does not increase the number when i need it to be 15-0002 etc.

The code is activated when they click on the textbox. If anyone can help I would be gratefull.

Private Sub IDnumber_Click()
 Dim CurrentYear As String
 Dim YearofILastIDnumb As String
 
 YearofILastIDnumb = ????????
 CurrentYear= Format(Now(), "yy")
 
 If YearofILastIDnumb = Null Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If
 
 If CurrentYear > YearofILastIDnumb Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If
 
 If CurrentYear = YearofILastIDnumb Then
        If Forms!frmNewContract!subLastIDnumber!LastIDnumber = CurrentYear & "-0001" Then
        Me.IDnumber.Text = Forms!frmNewContract!subLastIDnumber!LastIDnumber + 1
        Else
        Me.IDnumber.Text = Forms!frmNewContract!subLastIDnumber!LastIDnumber + 1
        End If
 
 End If
 
End Sub

 

[Minty]

You have a problem that your ID is actually a text field, and you are trying to add numbers to it.
You will need to extract the number portion from the end of your string - then convert and add 1 to it then add it back into the ID field.

Personally I would avoid this type of numbering system, I would stick to a simple unique ID number (that is a number). There is almost certainly a date field in your records that would enable you to display the year and id number if it aids your users.

 

[Acropolis]

Use a normal autonumber ID field for the record ID to make life easier.


If it were me, I would just use that as my ID number, but if you want to keep it your way, then use the autonumber, and then do the following.


Have a field in the record for the date it was created, or received, whichever is applicable to get the year value you are looking for. You can the use this and

 Format([DateField],"yy")

to get your two digit year format.


You could then use a dcount for the year of the datefield you are looking for form the table, and + 1 to it to get the rest of your number, then store this against the record as string value.

 ShipmentNumber = Format([DateField]","yy") & "-" & format(dcount("[DateField]","table","Year([DateField] = #"& [DateField] &"#") +1,"0000")

Haven't tested the above code, but it should get you going in the right direction hopefully.

 

[guestbb]

Thanks, i think i will do it manually.

 

[guestbb]

Okay so I made it work. Here is it for you if you maybe need it.
With Left and Right I grabbed the parts from the last shipment number. On Right I made the last 4 digits an integer so it could grow. rest is just checking for differences in years and etc. Hope this helps someone.

 Dim CurrentYear As String
 Dim YearOfLastSH As String
 Dim NumSH As Integer
 Dim FNumSH As String
 Dim NumNewSH As Integer
 
 YearOfLastSH = Left(Forms!frmNewContract!subLastIDnumber!LastIDnumber, 2)
 CurrentYear = Format(Now(), "yy")
 
 If YearOfLastSH = Null Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If
 
 If CurrentYear > YearOfLastSH Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If
 
 NumSH= Right(Forms!frmNewContract!subLastIDnumber!LastIDnumber, 4)
 NumNewSH = NumSH+ 1
 
 FNumSH= CurrentYear & "-" & Format(NumNewSH, "0000")
 
 If CurrentYear = YearOfLastSH Then
        If Forms!frmNewContract!subLastIDnumber!LastIDnumber = CurrentYear & "-0001" Then
        Me.IDnumber.Text = FNumSH
        Else
        Me.IDnumber.Text = FNumSH
        End If
 
 End If

 

[Acropolis]

Few problems in your code:

YearOfLastSH = Left(Forms!frmNewContract!subLastIDnumber!LastIDnumber, 2)

  If YearOfLastSH = Null Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If

Even though you check if it's null later in the code, if the control you are referencing is nul it will error as soon as it can't assign anything to it if the control you are referencing is null, so you will never get as far as

 If YearOfLastSH = Null Then

which should be

 If IsNull(YearOfLastH) Then

but the problem will still happen anyway.

  If CurrentYear > YearOfLastSH Then
    Me.IDnumber.Text = CurrentYear & "-0001"
 End If

Should be

 If CurrentYear > YearOfLastSH Then
    Me.IDnumber = CurrentYear & "-0001"
 End If

If CurrentYear = YearOfLastSH Then
        If Forms!frmNewContract!subLastIDnumber!LastIDnumber = CurrentYear & "-0001" Then
        Me.IDnumber.Text = FNumSH
        Else
        Me.IDnumber.Text = FNumSH
        End If
 
 End If

Should be:

 If CurrentYear = YearOfLastSH Then
        If Forms!frmNewContract!subLastIDnumber!LastIDnumber = CurrentYear & "-0001" Then
            Me.IDnumber = FNumSH
        End If
 
 End If

If you insist on doing it this way, then an easier way to do it is :

Private Sub Command4_Click()

If Len(Nz(Forms!frmNewContract!subLastIDnumber!LastIDnumber, vbNullString)) = 0 Then 'Check last number id has a value by the length of the field
    Me.IDnumber = Format(Date, "yy") & "-0001" 'if it doesnt start from 0001 for current year
Else
    If Format(Date, "yy") > Left(Forms!frmNewContract!subLastIDnumber!LastIDnumber, 2) Then 'check if current year is greater than last number id year
        Me.IDnumber = Format(Date, "yy") & "-0001" 'if it is start from record 0001 for current year
    Else
        Me.IDnumber = Format(Date, "yy") & "-" & Format(Right(Forms!frmNewContract!subLastIDnumber!LastIDnumber, 4) + 1, "0000") 'otherwise increment by 1
    End If
End If

End Sub

Or if you have a date field in your table where the entry is stored, you can use the following to get the same result:

Me.IDnumber = Format(Date, "yy") & "-" & Format(DCount("[ID]", "tblTest", "[CreatedWhen]>= #" & DateSerial(Year(Date), 1, 1) & "# AND [CreatedWhen]<= #" & DateSerial(Year(Date), 12, 31) & "#"), "0000")

It's also a good idea to use a prefix to your control names, makes life a lot easier identifying them in the code, txt = Textbox, cob = Combo Box, lst = List Box etc.

HTH

 

[guestbb]

Thank you sir