Solved How to format ascending numbers for different inputs?

[asteropi]

So I need to format a cell to create lot numbers.
I want it to be the SupplyID-Date-Ascending number

I have figured how to get the first two parts, but the last part needs to be an ascending number for every different ingredient, so I can't use the lot autonumber for instance.
For example, I have 2 ingredients

My lots should be like this:
Ingredient 1: 001(supplyID)-241012(Date)-01, 001-241012-02
Ingredient 2: 002-241012-01, 002-241012-02 etc


For the first 2 parts the code I wrote is like this:

If IsNull(Me.LotNumber) Then
    Me.LotNumber = Format(Me.SupplyID, "000") & Format(Date, "yymmdd") & Format (I need this part)
End If

How can I do that so that it will recognise it's a different ingredient every time and continue from where it left off?

 

[arnelgp]

Dim sLastLot As String
Dim sFirstPart As String
Dim var As Variant
sFirstPart =  Format(Me.SupplyID, "000") & Format(Date, "yymmdd") & "-"
If IsNull(Me.LotNumber) Then
    sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
    If Len(sLastLot) <> 0 Then
        var = Split(sLastLot, "-")
        var(2) = Format$(Val(var(2))+1, "00")
        Me.LotNumber = Join(var, "-")
   Else
       Me.LotNumber = sFirstPart & "01"
   End If
End If

 

[asteropi]

Dim sLastLot As String
Dim sFirstPart As String
Dim var As Variant
sFirstPart =  Format(Me.SupplyID, "000") & Format(Date, "yymmdd") & "-"
If IsNull(Me.LotNumber) Then
    sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
    If Len(sLastLot) <> 0 Then
        var = Split(sLastLot, "-")
        var(2) = Format$(Val(var(2))+1, "00")
        Me.LotNumber = Join(var, "-")
   Else
       Me.LotNumber = sFirstPart & "01"
   End If
End If

I had 4 inputs already, but it starts from 01 again.
Also, after 01, it loses the - and then everything it puts is 02, no matter how many lines I add

PS. I made it to add a - after the supplyID (here 016-)

 

[arnelgp]

you need to change the code to accept your Date field (on the code you change YourDateField:

Dim sLastLot As String
Dim sFirstPart As String
Dim var As Variant
sFirstPart =  Format(Me.SupplyID, "000") & Format(Me.YourDateField, "yymmdd") & "-"
If IsNull(Me.LotNumber) Then
    sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
    If Len(sLastLot) <> 0 Then
        var = Split(sLastLot, "-")
        var(2) = Format$(Val(var(2))+1, "00")
        Me.LotNumber = Join(var, "-")
   Else
       Me.LotNumber = sFirstPart & "01"
   End If
End If

 

[arnelgp]

sorry missing "-" on sFirstPart, change to:

    Dim sLastLot As String
    Dim sFirstPart As String
    Dim var As Variant
    sFirstPart = Format(Me.SupplyID, "000") & "-" & Format(Me!YourDateField, "yymmdd") & "-"
    If IsNull(Me.LotNumber) Then
        sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
        If Len(sLastLot) <> 0 Then
            var = Split(sLastLot, "-")
            var(2) = Format$(Val(var(2)) + 1, "00")
            Me.LotNumber = Join(var, "-")
       Else
           Me.LotNumber = sFirstPart & "01"
       End If
    End If

 

[asteropi]

now it keeps the - and it ascends properly but it goes back to 01 if I change date.
I was thinking it should continue on and keep the previous number. Or shouldn't it??
I really don't know what's the best option

 

[arnelgp]

I was thinking it should continue on and keep the previous number.

which event are you using the code, You need to put it in the BeforeUpdate event of the form.
also on design view of your form you set Locked property of LotNumber to Yes.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim sLastLot As String
    Dim sFirstPart As String
    Dim var As Variant
    sFirstPart = Format(Me.SupplyID, "000") & "-" & Format(Me!YourDateFieldHere, "yymmdd") & "-"
    If IsNull(Me.LotNumber) Then
        sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
        If Len(sLastLot) <> 0 Then
            var = Split(sLastLot, "-")
            var(2) = Format$(Val(var(2)) + 1, "00")
            Me.LotNumber = Join(var, "-")
       Else
           Me.LotNumber = sFirstPart & "01"
       End If
    End If
End Sub

 

[asteropi]

which event are you using the code, You need to put it in the BeforeUpdate event of the form.
also on design view of your form you set Locked property of LotNumber to Yes.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim sLastLot As String
    Dim sFirstPart As String
    Dim var As Variant
    sFirstPart = Format(Me.SupplyID, "000") & "-" & Format(Me!YourDateFieldHere, "yymmdd") & "-"
    If IsNull(Me.LotNumber) Then
        sLastLot = DMax("LotNumber", "YourTableName", "LotNumber Like '" & sFirstPart & "*'") & ""
        If Len(sLastLot) <> 0 Then
            var = Split(sLastLot, "-")
            var(2) = Format$(Val(var(2)) + 1, "00")
            Me.LotNumber = Join(var, "-")
       Else
           Me.LotNumber = sFirstPart & "01"
       End If
    End If
End Sub

I put it in the beforeUpdate and I locked the lotnumber

However, it still goes to 01 for any new date

 

[arnelgp]

However, it still goes to 01 for any new date

then put the Date instead of your DateField:

..
sFirstPart = Format(Me.SupplyID, "000") & "-" & Format(Date(), "yymmdd") & "-"

 

[asteropi]

then put the Date instead of your DateField:

..
sFirstPart = Format(Me.SupplyID, "000") & "-" & Format(Date(), "yymmdd") & "-"

still the same
I think this line is the problem:

Else
Me.LotNumber = sFirstPart & "01"

 

[arnelgp]

remove all the LotNumber manually (dont' use the form, use the table).
open the form and Edit each record so that the LotNumber is filled again.

 

[asteropi]

remove all the LotNumber manually (dont' use the form, use the table).
open the form and Edit each record so that the LotNumber is filled again.

same no luck

 

[arnelgp]

when (what condition) will the Lotnumber go back to 1?

 

[asteropi]

when (what condition) will the Lotnumber go back to 1?

if i change the date it goes back to 01. it shouldn't do that. it should keep on going from the previous lot regardless of the date

 

[arnelgp]

change this line and see if it makes a difference

    If IsNull(Me.LotNumber) Then

to

    If Len(Trim$(Me.LotNumber & "")) = 0 Then

 

[arnelgp]

if i change the date it goes back to 01

what Date are you talking here? if you mean computer date, it will naturally go back to 1, since the function is based on Date (computer date).

 

[asteropi]

change this line and see if it makes a difference

    If IsNull(Me.LotNumber) Then

to

    If Len(Trim$(Me.LotNumber & "")) = 0 Then

no difference

 

[arnelgp]

then i don't know what to do also.
maybe someone will figure it out.

 

[CarlettoFed]

Maybe if you attach a sample file, as always, it will be easier to help you.

 

[CJ_London]

think you need to provide some example data as you have duplicate dates and from what is shown you consequently have no way to identify a unique record for your counter.

I want it to be the SupplyID-Date-Ascending number

it needs to be

I want it to be the SupplyID-Date-LotID?-Ascending number