Help with incrementing field

[kebab]

I have a text field that looks like 07-08-001 (07 for year, 08 for month, and 001 for record number.

How can I automatically increment to the next text/number (eg. 07-08-002) when I bring up a new record? Or is this not possible.

I also have to be able to start a new text/number each month (eg. 07-09-001) manually.

 

[WayneRyan]

kebab,

Have 1 date field (TheDate) and one sequence field (Sequence).

Default the date to Now() and in the BeforeInsert event of your form put:

Sequence = Nz(DMax("[Sequence]", _
                   "YourTable", _
                   "DatePart("yyyy", [TheDate]) = DatePart("yyyy", Now()) And " & _
                   "DatePart("m",    [TheDate]) = DatePart("m", Now())), 0) + 1

Whenever you want to display:

Format([TheDate], "yy-mm-") & CStr(Sequence)

hth,
Wayne

 

[kebab]

Thanks for the quick reply. I am sort of new at this.

My database is "plans.mdb"
My only table is "plans"
My only form is "plans"
The field in question is "plan check #"

As I understand it:
I add a date field called, let's say, "Current Date" with a default value of Now()
I add a number field called, lets say, "Sequence Number"

This is were I lose it. I open the design view of the form "plans" and paste the code into, under "event", "before insert"? Using "code builder"?

In the code do I replace "Your Table" with "plans"? Maybe you can rewrite the code using the information above.

Finally the: Format([TheDate], "yy-mm-") & CStr(Sequence)
Do I paste this into the default value of "plan check #"?

 

[Dennisk]

This is a standard solution offered on this form but how does Access handle multi user concurrency using the DMAX function. Is it possible to generate the same sequence number?

 

[neileg]

This is a standard solution offered on this form but how does Access handle multi user concurrency using the DMAX function. Is it possible to generate the same sequence number?

The way I have tackled it is to generate the number as the last event in creating the record before save. This reduces conflicts to virtually zero. If the field is set to indexed, no duplicates this will obvious catch the occasions when a duplicates is created.

 

[Dennisk]

Neil,

I used to maintain a db of 70,000 patients and the DMAX function was used to generate the next number and yes they never had a problem.

However virtually zero is not zero so it has to be catered for.

So if jet reports a duplicate then the user has to click on the error message then try again.

When I develop db's I do not reply on Access error messages at all as I trap all the problems before Jet does, so I would place this code in a loop so I can test if a duplicate has been created then If so try again.

 

[neileg]

Dennisk, I agree with your view, I just didn't spell it out like that. However, I would still index the field even after applying my own validation - just to be sure!