need to create incremental file number 1

[compucop]

Hi, I am looking for help with the following problem.
I am trying to create a incremental file number with each individual record. The file number always consists of the last two digits of the current year 02- and an incremental numbers starting at 1 to whatever. This is used for a police department, the case system tracks each individual case that we respond to. the first case will be 02-1 and then so on. At the end of the year, the 02 will go to 03 (for 2003) and the number will start at 1 again. I was able to get the last two year numbers by using =format(date()"yy-&quot, but I cannot generate an incremental number. I also have to have the number revert back to 0 in on January 1.
Any help will be greatly appreciated. Thanks.

 

[johncb]

Create a table with two fields: NextNumber and SetDate.

Each time you create a record you get the NextNumber, save it with the YY from format(date()"yy-&quot and rewrite NextNumber to its table after incrementing it by 1.

In the OnLoad event of your main form put in some code to compare the YEAR of the SetDate field with the YEAR from DATE. If the YEAR in DATE is greater than the YEAR in SetDate then rewrite NextNumber as 1.
eg
SavedYY = format(SetDate()"yy-&quot
ThisYY = format(date()"yy-&quot
If ThisYY > SavedYY Then
NextNumber = 1
end if



hth

John

 

[compucop]

John, thanks for the help. I will give it a try tonight. I still will probably have the problem with creating the sequential numbering. the code for the change of year looks great. Thanks again. I will post with my result. Dave H.

 

[LarryDeLaruelle]

Compucop:

Here is the code I use to set an incremental numbering system. I use the full year but you should be able to adapt this to your needs. It does account for roll-over to a new year. Most of my db's contain some form of record date and I use the After Update event of that control to run the code.

Private Sub RecDate_AfterUpdate()
Dim strLastNum As String

'If this is the first record for the database, this will set the initial value
If IsNull(strLastNum = DMax("RecID", "tblRecNumTest&quot) Then
RecID = DatePart("YYYY", Date) & "0001"
Exit Sub
'If records exist, strLastNum is loaded with the highest record number value
Else
strLastNum = DMax("RecID", "tblRecNumTest&quot
End If

'If the new record's year is greater than the year part of the highest record number,
'the user is given the option of resetting the counter for the new year or continuing
'to use the existing year with the new record number being 1 greater than the highest.
'If the new record's year is not greater, the new record number is incremented by one
'from the highest.
If Val(DatePart("YYYY", RecDate)) > Val(Left$(strLastNum, 4)) Then
If MsgBox("WARNING You are about to reset the Record Counter" & Chr(13) & Chr(13) _
& "Are you sure you want to do this?", vbOKCancel, "WARNING -- WARNING -- " _
& "WARNING -- WARNING&quot = vbCancel Then
RecID = Val(strLastNum) + 1
Else
RecID = DatePart("YYYY", RecDate) & "0001"
End If
Else
RecID = Val(strLastNum) + 1
End If

End Sub

One note. This code does not test for dates that are earlier than the current year (past year's records).

Hope this helps. Feel free to post again or e-mail me direct if you have any questions.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[compucop]

Larry, Thanks for the info. I ran your code and it helped me a bit. But I need the numbers to reset without the user having to input the date. I played with your information and that of John's and came up with the following solution.
I used the =dmax function to give me the incremental number. I used the format(date(),yy-&quot for the year prefix. This worked fine, but to get the field to start over at the first of the year, i created a SQL delete statement and deleted the incremental number table so that it would start at 1 again. Now, I just have to devise a way for this code to run on January 1st. Thanks I appreciate the help.

David Hinch

 

[LarryDeLaruelle]

David:

Glad I helped. Will this get me a pass on my next speeding ticket? Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[compucop]

Larry, No problem... For all your help you can commit any traffic violation that you wish. Thanks again. I got that to work, thanks for your help. Take Care. David Hinch