# 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")
###### Who is Participating?
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.

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

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

NewNum2 = Nz(DMax("LastLotN", "qryLastLotNumber"),0)
0
CIOCommented:
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
``````
/gustav
0

Experts Exchange Solution brought to you by

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

Commented:
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!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
``````
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.
0
Author 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
0
Commented:
0
Author 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
``````

Query SQL:

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

/gustav
0
Commented:
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.
0
Author Commented:
Gustav.  your code gave me SPyy-00001 for the first one this current year.  The yy should be 15.
0
Think that format date in Gustav's code would have to be: CurrentYear = Format(Date(),"yy")
0
CIOCommented:
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
0
Commented:
Gus,
I thought that was what I said.  Is it too early in the morning for you or perhaps too late at night LOL
0
CIOCommented:
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
0
Author 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
``````
0
Author Commented:
It is working.  I just want to be sure I have it right.
0
CIOCommented:
You have, but at second sight, you can reduce this line to:

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

/gustav
0
Commented:
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.
0
CIOCommented:
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
0
###### 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.