Still Learning, Need advice on old problem

[sdchris]

I read through this thread and implemented a few features that were suggested by Pat.

However, I'm still having trouble grasping how I can get the incrementing number to reset at the start of a new year. I would prefer for it to continue on each year, but my boss likes it to reset.

What I need is the year, and then an incrementing number after that. Example:

2006-0001
2006-0002
2006-0003

2007-0001
2007-0002
2007-0003

I have three fields in my table. One to store the year; one to store the incrementing number; and the last to store the combined year and number. I want to check the year and then increment the number +1 for the incrementing number for that year. If it is a new year, the numbering will start at 1.

My code is as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim nextNum As String, myYear As String

myYear = Format(Year(Date))

If Me.NewRecord Then
On Error Resume Next

'incrementing number
nextNum = Nz(DMax("[txtIncrement]", "[tblTest]", [txtYear] = myYear), 0) + 1
Me.txtIncrement = nextNum
'combining the results and storing it in output
Me.txtOutput = myYear & "-" & Format(nextNum, "0000")

End If

End Sub


I'm still fairly new to vba so any help would be tremendous. If I missed a thread that has better step-by-step information, then please let me know. This forum has been great.

 

[Mr. B]

You really should not have to do anything. The code you have always reads the current year and then the line:

nextNum = Nz(DMax("[txtIncrement]", "[tblTest]", [txtYear] = myYear), 0) + 1

will handle the resetting of the incrementing number. The "NZ" makes the null value that will always be returned by the DMax function when the first new number for the new yeare is being created. This means that if there is not a value in the table that can be returned by DMax for the current year then a zero is returned and then 1 is added to the returned value.

 

[pbaldy]

You have to concatenate the year (plus you were missing some quotes):

DMax("[txtIncrement]", "[tblTest]", "[txtYear] = " & myYear)

 

[sdchris]

Thanks for the replies. I tried out your code PBaldy, but it had a different result. Instead of incrementing the numbers, it kept them the same.

I can get the number to increment with access and then concatenate that with the year, but I can't get the code right to read the year, find the next increment value for that year, and then increment it +1. My code just automatically inserts the next highest number +1, regardless of year.

For example, here is my output over a few random test years. ID 1-4 is with my code. 5-7 is with the quotes from PBaldy. And 9-11 is back to my code.

ID txtYear txtIncrement txtOutput
1 2003 1 2009-0001
2 2008 2 2009-0002
3 2009 3 2009-0003
4 2007 4 2009-0004
5 2006 1 2009-0004
6 2007 5 2009-0004
7 2006 4 2009-0004
8 2006 5
9 2008 6 2009-0006
10 2008 7 2009-0007
11 2005 8 2009-0008

Note that it didn't paste correctly. Thanks again for your help.

 

[pbaldy]

Mine was intended to be incorporated into your original code with the Nz() and "+ 1". Sorry if that wasn't clear.

 

[sdchris]

I was baffled so I got our IT department to help with the database. It looked like he wanted to punch me when I said we wanted to use access.

I'll post the code that he used to implement these features when he brings me back our access database.

Regards,
Chris

 

[pbaldy]

Some IT people have issues with Access. I'd tell him you don't want to wait 2 years for a non-Access solution, and then 6 months for trivial changes to that solution.

This should work:

nextNum = Nz(DMax("[txtIncrement]", "[tblTest]", "[txtYear] = " & myYear), 0) + 1

but it will be interesting to see what he comes back with. The above presumes txtYear is a field in the table, and is of a numeric data type.