Rick Danger
asked on
Microsoft Access - need to add a counter for each record
I have a requirement where I have to add a document number for each record. The sequential number is unique for each year, and is 3 chars long. So if we are on number 187 now, the next record is to be 2015/188 and so on. Then the first record for next year will be 2016/001. And so on...
What is the best way to do this please?
What is the best way to do this please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks - nice easy answer to follow
I prefer to store this value in a separate table (tbl_NextValue) and use a function to update that value. This table can contain values for multiple tables/IDs and might would look something like:
ValueName(text) ValueYear(integer) NextValue(long int)
DocNumber 2015 188
InvNumber 2015 0
Then you would use a function like the following to get the new value. This assumes that you actually want the year appended to the beginning of the next value:
ValueName(text) ValueYear(integer) NextValue(long int)
DocNumber 2015 188
InvNumber 2015 0
Then you would use a function like the following to get the new value. This assumes that you actually want the year appended to the beginning of the next value:
Public fnNextValue(ValName as string, Optional ValYear as Variant = NULL) as string
Dim rs as DAO.Recordset
Dim strSQL as string
if isnull(valYear) then valYear = Year(Date())
strSQL = "SELECT * FROM tbl_NextValue " _
& "WHERE [ValueName] = '" & ValName & "' " _
& "AND [ValueYear] = " & clng(ValYear)
set rs = currentdb.OpenRecordset(strsql, , dbfailonerror)
if rs.eof then
.AddNew
!ValueName = ValName
!ValueYear = valYear
!NextValue = 2
.Update
fnNextValue = 1
Else
fnNextValue = !NextValue
.Edit
!NextValue = !NextValue + 1
.Update
End IF
ProcExit:
on error resume next
rs.close
set rs = nothing
Exit Function
ProcError:
debug.print "fnNextValue", err.number, err.description
msgbox err.number & vbcrlf & err.description, , "fnNextValue error"
Resume ProcExit
Exit Function
This actually stores the next value to be retrieved in the table and will insert a new record in the table when the year changes or when you provide a new year in the function.
Ultimately, this data should be stored in separate fields
The incremented number should be an AutoNumber field, ...and the year should just be pulled form the current year
Then you can concatenate the two for display purposed.
As he field is now, it is text, ...and there fore hard to query for a specific DocNumnber based on the year.
(or a range of DocNumber/years)
Also investigate Dales Post, ...as there are many ways to do this...
Jeff
The incremented number should be an AutoNumber field, ...and the year should just be pulled form the current year
Then you can concatenate the two for display purposed.
As he field is now, it is text, ...and there fore hard to query for a specific DocNumnber based on the year.
(or a range of DocNumber/years)
Also investigate Dales Post, ...as there are many ways to do this...
Jeff
ASKER
Thanks to you both - I actually like Dale's answer, but I've already given out the points!
what will you do if a record somewhere at the middle was deleted?
do you still want to see a continuous counter or it is okay to have a gap?
do you still want to see a continuous counter or it is okay to have a gap?
ASKER
The gap will have to remain.
no problem WRT points. good luck with your project.
Make a field called DocNum in the table, set the datatype as Long Integer
Make the first record a value of 1.
Then use code like this to increment the values and add the slash and the year
Public Function GetNextDocNumber () as string
Dim lngMaxDocNum as long
lngMaxDocNum =Dmax("DocNum", "YourTable")
GetDocNumber =lngMaxDocNum +1 & "/" & Year(Date())
End Function