Need help creating an increment field VBA

[carbo]

I need to create a field named “casenumber” that will contain increment number value for each record (similar to “AutoNumber”) but I need it to reset to 0001 at the first of each month. I am using Access 2000.

Thanks in advance

[Ecniv]

No idea why you'd want to reset it.




Make a table of one row and one column. Put your number in there. When saving the new record, get the number from this table, increment it and update that then create your new record with the number you retrieved.

Then every month you can change the number back to 0... or 1...

[carbo]

This field is used in a database that tracks criminal offences on a monthly basis. Each occurrence is identified by a “case number” which is broken down as follows: 06-03-0001 .06 = Year 03 = Month 0001 represents the individual criminal activity for that time period.

Again thanks for any help

carbo

[Webtest]

I don't have time to work on this right now, but one approach is to set up a Global variable for the Item Sequence number (case number) and a Global Boolean flag "gb_Updated".

Set up a macro that runs when the workbook opens. This is NOT code ... it is just what I wold have the macro do:

If (day of month) > 15 then gb_Updated = False

if (day of month <= 15) AND (gb_Updated = False) then
gi_CaseNum = 1
gb_Updated = True

Are you guaranteed to have at least ONE case during the first half of the month? This will update the Case Number only One Time anytime during the first half of the month (it doesn't have to be on the very first day of the month!). Can you take it from here? If not, I'll post more Wednesday.

[carbo]

Typically there are from 800-1500 entries per month.

Thanks

carbo

[KGComputers]

Hi,

How many criminal offenses do you process every month? Are you creating a program for a trial court?Prison?

Thanks....

[carbo]

This MsAccess 2000 database is for a 911 Center that assigns case numbers to various law enforcement agencies as well as other emergency services type disciplines. It is a centralized repository for “Master Name Index” information.

[RobDog888]

I would recommend that you get them off of Acccess then as its not really scallable enough and is subject to corruption issues if your not careful in its design and code access.

[carbo]

Would it be your assertion that a MsAccess database, created in a “flat file” format, be equally susceptible to corruption as a “relational” format?

[RobDog888]

A flat file would be stable but it is also a nightmare to manage.

[carbo]

I still have not resolved this issue; any and all help will be greatly appreciated.

Thanks

Carbo