Neeed help incrementing a field by 1

That would be Format(Date(),"yy") so like this:

Me.txtSAIlotN = "SP" & Format(Date(),"yy") & "-" & Format(CInt(NewNum2) + 1, "00000")

You need to handle nulls or the first record of the year will fail.

 NewNum2 = Nz(DMax("LastLotN", "qryLastLotNumber"),0)

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

Select allOpen 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.

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

Your criteria is not correct.  Please post the query.

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

Select allOpen in new window

Query SQL:

SELECT Right([LotN],InStr([LotN],"-")) AS LastlotN
FROM tblTaskCardHeader;

Why don't you use my simple code posted previously - it just works.

/gustav

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.

Gustav.  your code gave me SPyy-00001 for the first one this current year.  The yy should be 15.

Think that format date in Gustav's code would have to be: CurrentYear = Format(Date(),"yy")

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

Gus,
I thought that was what I said.  Is it too early in the morning for you or perhaps too late at night LOL

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

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

Select allOpen in new window

It is working.  I just want to be sure I have it right.

You have, but at second sight, you can reduce this line to:

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

/gustav

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.

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