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:

Code:
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?