Question Push Button to increment, will add one to category

[tedward]

How do I:

‘This code increments a number starting with one
Me.VMD.Value = Nz(Me.VMD.Value, 0) + 1
‘This code changes the year in my table after Sep. 30th of each year
StartDate = 2012
If Date > DateSerial(StartDate, 10, 0) Then YearDate = Year(Date) + 1
If Date < DateSerial(Year(Date), 10, 1) Then YearDate = Year(Date)

How is the best way to capture each record in my Access table and reset the number to 1 when the year changes
Example: My table would like this
Record 2012-001
Record 2012-002
Record 2012-002 and so on until the next year change


Record 2013-001 New year change
Record 2013-002 until the next year change


Record 2014-001


I will appreciate any input


Thanks, Tedward

 

[vbaInet]

Are you going to present this in a report? It would be easy to do in a report using the Grouping and Running Sum.

If you want to do it in a query, search the forum for Running Count in the Query section. The solution implements the DCount() function.