Incremental numbering of items by year

[rmorlan]

I don't really know much code, so I have no idea where to begin.

I have a table (DocTable) for library documents: title, year, document ID number (just an autonumber) and file number. The file number field is what I need help with. I need the documents numbered in the order that they will be entered into the database, but separated by year, so that for each year the numbering restarts at 1.

Because I can't figure out how to do this, I have a separate FileNo field for each year: FileNo1990, FileNo1991, etc. I'd really like to have only one field, if possible.

I tried this code for each year, but since I don't really know what I'm doing, I don't know where I went wrong:

UPDATE DocTable
SET FileNo1990 = FileNo1990 + 1
WHERE Year = 1990

If someone could show me the proper code, it would be greatly appreciated.

Thanks!

Rachel

 

[straybullet]

Check out

http://tek-tips.com/viewthread.cfm?qid=1271071

Let them hate - so long as they fear... Lucius Accius

 

[rmorlan]

That didn't work for me. The FileNo field incremented, but not by year -- it just added 1 to the highest value it found.

My data set looks like this:
Year FileNo
1990 1
1991 1
1992 1
1992 2
1993 1
1994 1

With the code from the referenced thread, the next value for FileNo is 3, regardless of the Year value. What do I need to add or change to have it look at the FileNo value by year? Is that even possible?

Thanks,
Rachel

 

[straybullet]

Me.FileNo = DMax("[FileNo]", "tablename", "[YearFieldName]=Form![YearFieldName]") + 1

The above is an edited version of the code behind a form's OnOpen event in a currently used db. Something along these lines would allow you to reference the Year field of the record you are currently dealing with.

Let them hate - so long as they fear... Lucius Accius