Case AutoNumber

[danassef]

How can I set up a table so that as we assign a case it generates a new number with the year in front of it. I would like for the case number to be generated by the program and when someone enter a new case the program generates the next case number and assignes it to that case. The format we use for our file numbers are year-XXXX (ex: 07-0001). With the new year I'm trying to set this up so that as we start the year the computer will generate the new number.

 

[WayneRyan]

d,

You'll need two fields in your table: TheYear and Sequence, both numeric.

On your form's BeforeInsert event:

Me.TheYear = DatePart("yyyy", Date)
Me.Sequence = Nz(DMax("[Sequence]", _
                   "YourTable", _
                   "[TheYear] = " & Me.TheYear), 0) + 1

Obviously, the user's cannot enter data in those fields on your form.
When you display, just concatenate the two fields:

CStr(TheYear) & "-" & CStr(Sequence)

Edited to add Nz & CStr.

hth,
Wayne