How to reset sequence number to 1 when new date occurs?

[eneyardi]

I have a form that I use to autogenerate a new projectid that has the format of: currentdate which is yyyymmdd & sequencenum. Example: 20081024-1

Below is the code I have on the current event of the form:

Private Sub Form_Current()
If Me.NewRecord Then
Me.project_sequencenum = DMax("project_sequencenum", "tblprojects") + 1
End If
Me.txtdatesequence = Me.project_date & "-" & Me.project_sequencenum
Me.txtdatesequence = Format(Me.project_date, "yyyymmdd") & "-" & Me.project_sequencenum

End Sub

The problem I am having is that the sequencenum automatically increments, but does not reset to 1 for the first projectid I create on a new date.
How do I have the sequencenum reset to 1 when a new date occurs so that the first projectid created starts with the sequencenum of 1?



Thanks,

[Steven Kogan]

The DMax function would need to specify the date or whatever criteria on which you want a new sequence. It returns null if there is no match, so use the nz function to assign 0 if it is null.

You'd want something like:

nz(DMax("project_sequencenum", "tblprojects","project_date="& format(me.project_date,"dd-mmm-yyyy")),0)+1

(I'm a bit clumsy with the date format, but this should work...)

[Steven Kogan]

By the way, as NeoPa pointed out, consider using the Before Insert event to generate the new key field. That way if someone else is adding a record at roughly the same time you are much less likely to get the same number.

Be sure the field is set as a primary key or indexed with no duplicates. That way if a duplicate sequence number is generated for the same day you will be prompted to save again. When you do, the before insert event would fire.

[eneyardi]

it didn't answer my question how to reset to 001 when new date occurs? anyone has a sample program sequence no. reset to 001 when new date occurs?

[colintis]

It looks to me the code you have is just getting the highest seq no. in the table, so it never resets to 1, and it will keeps incrementing.

So you should setup a IF statement in front to check whether the newest projectid in the table is equal to the current date, if yes then you simply set Me.project_sequencenum as 1, otherwise continue the original if statement you have in your own.

[Steven Kogan]

The code looks for the highest sequence number for a given date and adds one to it. If there are no projects for that date the DMax function returns null, the nz function returns 0, and 1 is added to it, thus restarting the sequence at 1.

This would not work if project_sequencenum is an autonumber field - instead the function is used to generate the number for you.

What is it about the code that doesn't work for you?

nz(DMax("project_sequencenum", "tblprojects","project_date="& format(me.project_date,"dd-mmm-yyyy")),0)+1