Neeed help incrementing a field by 1

A while back an expert helped me with the following code:

    If Me.NewRecord = True Then
    Dim NewNum2 As String
        NewNum2 = DMax("LastLotN", "qryLastLotNumber")
        Me.txtLotN = "SP14-" & Format(CInt(NewNum2) + 1, "00000")
    End If

But now the issue is I need to increment by 1 but the "SP14" part of it has to be replace with "SP" and the last two digits of the current year.  In other words if it was the first record then Me.txtSAIlotN would be "SP15-00001".

How would this line be re-coded to accomplish this?

Me.txtSAIlotN = "SP14-" & Format(CInt(NewNum2) + 1, "00000")
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
That would be Format(Date(),"yy") so like this:

Me.txtSAIlotN = "SP" & Format(Date(),"yy") & "-" & Format(CInt(NewNum2) + 1, "00000")
PatHartmanCommented:
You need to handle nulls or the first record of the year will fail.

 NewNum2 = Nz(DMax("LastLotN", "qryLastLotNumber"),0)
Gustav BrockCIOCommented:
It would imply a filtering on the year:

    Dim NewNum2 As Integer
    Dim CurrentYear As String
    If Me.NewRecord = True Then
        CurrentYear = Format("yy", Date)
        NewNum2 = DCount("LastLotN", "qryLastLotNumber", "LastLotN Like 'SP" & CurrentYear & "*'" ) + 1
        Me.txtLotN = "SP" & CurrentYear & Format(NewNum2, "\-00000")
    End If

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I would use two separate fields for that, one for LotYear and the other for LotNum, you can then concatenate them together in a query to present the data to the user.

I also am not in favor of this method of tracking and generating new lot numbers in a multi-user environment, because it is feasible that two users would generate the same number in a sequence like:
1.  User one generates a new lot number, which is then placed in a field on his form
2.  User one then gets a phone call or gets distracted and does not save his current record
3.  While user one is distracted, user two generates a new number (but it will actually duplicate the number generated by user one because user one has not save his record yet)
4.  Users one and two both save their records.  If there is no unique index on this field, then they will both be allowed to save the record, creating a duplicate in your database.

Instead, I generally use one of two methods:
1.  The simplest is to use your technique above, but only do it as part of the BeforeUpdate event of the form.  This will cause this field to be blank until the record is saved, but will ensure that you don't get any duplicates.

2.  The second stores these numbers in a separate table, and increments them immediately when the user retrieves them, preventing two users from retrieving the same value.  That table might look something like:

tbl_NextLot
LotYear    LotNumber
12              1300
13              2500
14              3000
15              2801

YOu would then write a function that would return the next value, something like:
Public Function fnNextLotNumber() as String

    Dim rs as DAO.Recordset
    Dim strSQL as string
    Dim intYear as integer

    intYear = Year(Date())
    strSQL = "SELECT LotYear, LotNumber FROM tbl_NextLot WHERE [LotYear] = " & intYear
    set rs = currentdb.openrecordset(strsql, , dbfailonerror)

    if rs.eof then
        rs.AddNew
        rs!LotYear = intYear
    else
        rs.Edit
    endif
    rs!LotNumber = NZ(rs!LotNumber, 0) + 1
    fnNextLotNumber = "SP" & Format(intYear, "00") & "-" & Format(rs!LotNumber, "0000")
    rs.close
    set rs = nothing

End Function

Open in new window

You could then call this function any time you want to get the next lot number and never have to worry about someone else getting the same number.  

Although it is nice to display this number on a form while the user is entering data, the best way to do it, if you are concerned with having records in your table with non-sequential numbers (caused by a user generating the data then failing to save the record) is to use the BeforeUpdate event to generate this number.
SteveL13Author Commented:
To all who have helped so far.  I changed my computer date and time clock to be 11/17/2016.  When I ran the code I expected to see SP16-00001.    Instead I got SP16-0292.  The last record was xxxx-0291.

If it is the first record of the new year I want the digits after the hyphen to be 00001.

--Steve
PatHartmanCommented:
Your criteria is not correct.  Please post the query.
SteveL13Author Commented:
Before Insert Code:

    If Me.NewRecord = True Then
    Dim NewNum2 As String
        'NewNum2 = DMax("LastLotN", "qryLastLotNumber")
        NewNum2 = Nz(DMax("LastLotN", "qryLastLotNumber"), 0)
        'Me.txtSAIlotN = "SP14-" & Format(CInt(NewNum2) + 1, "00000")
        Me.txtLotN = "SP" & Format(Date, "yy") & "-" & Format(CInt(NewNum2) + 1, "00000")
    End If

Open in new window


Query SQL:

SELECT Right([LotN],InStr([LotN],"-")) AS LastlotN
FROM tblTaskCardHeader;
Gustav BrockCIOCommented:
Why don't you use my simple code posted previously - it just works.

/gustav
PatHartmanCommented:
Gus,
I agree BUT, you should use DMax() rather than DCount() for safety AND that means that you have to account for null as I suggested.

DCount() fails if records are ever deleted.  DMax() always works.  So regardless of whether you allow deletes or not, DMax() is better practice since you don't have to think about it and you don't have to worry if the business rules change in the future.  However, DMax() can return null if no match is found as would happen for the first record of a new year so you have to use Nz() to get past that hurdle.
SteveL13Author Commented:
Gustav.  your code gave me SPyy-00001 for the first one this current year.  The yy should be 15.
Gerwin Jansen, EE MVETopic Advisor Commented:
Think that format date in Gustav's code would have to be: CurrentYear = Format(Date(),"yy")
Gustav BrockCIOCommented:
Exactly. Thanks. It was, of course, air code.

And, Pat, it's the other way round: DCount always will return a number while DMax returns Null for an empty record set.

/gustav
PatHartmanCommented:
Gus,
I thought that was what I said.  Is it too early in the morning for you or perhaps too late at night LOL
Gustav BrockCIOCommented:
No, I'm perfectly fine, and I had to read twice what you wrote. I've done so again, but I still read the same:

> DCount() fails if records are ever deleted.  DMax() always works.  

So - after having had your morning coffee - what do you think now?

/gustav
SteveL13Author Commented:
This is what I have.  Is it ok?  Or is Count supposed to be Max?  (I'm confused)

    Dim NewNum2 As Integer
    Dim CurrentYear As String
    If Me.NewRecord = True Then
        CurrentYear = Format(Date(),"yy")
        NewNum2 = DCount("LastLotN", "qryLastLotNumber", "LastLotN Like 'SP" & CurrentYear & "*'" ) + 1
        Me.txtLotN = "SP" & CurrentYear & Format(NewNum2, "\-00000")
    End If

Open in new window

SteveL13Author Commented:
It is working.  I just want to be sure I have it right.
Gustav BrockCIOCommented:
You have, but at second sight, you can reduce this line to:

    NewNum2 = DCount("*", "qryLastLotNumber", "LastLotN Like 'SP" & CurrentYear & "*'" ) + 1

/gustav
PatHartmanCommented:
aha, Maybe I should be more clear.  What I meant by:

DCount() fails if records are ever deleted.  DMax() always works

Is that DCount() could give a wrong result as the next number, not that it wouldn't return any value.  So if you have 1, 2, 3 already assigned and you delete 1, DCount() will return 2 so 2+1 = 3 and that is a duplicate of an existing ID.  Whereas DMax() will return 3 since that is the highest value so 3+1 = 4 which would be the correct new ID.  If you delete 3, DMax() will return 2 so 2+1 = 3 and it will reassign 3 to a new record which may or may not be an issue so, know your business rules and act accordingly.  Since I use cascade delete for my relationships with child tables, anything originally tied to the first #3 would have been deleted so the table data would be consistent but there may be paper documents that would conflict.  Therefore, if you print this number on paper documents, I would strongly recommend soft deletes rather than hard deletes so no record is ever physically deleted or your paper trail could get messed up.
Gustav BrockCIOCommented:
Ah, that makes sense - what a relief.

Yes, records should never be deleted but be marked as excluded , and then be filtered out when retrieved for forms or reports.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.