I'd strongly recommend that you use two separate fields, both integer
numbers. ProjectYear and ProjectNumber say. You can make this the composite
primary key of the table; in table design view click on the field selector
(the little grey square to the left of the field name) and drag down until
both fields are highlighted. Then right click and select primary key from
the shortcut menu. One word of warning her, though. If you already have a
field such as ProjectDate from which the year can be derived, don't create a
ProjectYear column; that introduces redundancy which is 'a bad thing'.
Instead make the ProjectDate and ProjectNumber fields the composite primary
key.
If you do use a ProjectYear field want the year to be the current year at
time of the creation of the project record then you can automatically compute
both the ProjectYear and ProjectNumber value in the data entry form's
BeforeInsert event procedure with the following code:
Dim strCriteria As String
Me.ProjectYear = Year(VBA.date)
strCriteria = "ProjectYear = " & Me.ProjectYear
Me.ProjectNumber = Nz(DMax("ProjectNumber", "Projects", strCriteria),0)
where projects is the name of the table.
If you have a ProjectDate field, or similar, then instead pf the above, in
the AfterUpdate event procedure of the ProjectDate control on the form put:
Dim strCriteria As String
strCriteria = "ProjectYear = " & Year(Me.ProjectDate)
Me.ProjectNumber = Nz(DMax("ProjectNumber", "Projects", strCriteria),0)
In either case set the format property of the ProjectNumber control to 000.
One caveat: while looking up the highest project number for the year like
this is fine in a single user environment, in a multi-user environment on a
network there is a risk of conflicts if two or more users are adding records
simultaneously, in which case the data error which results would have to be
handled in the form's Error event procedure. Something along these lines:
Const DUPKEY = 3022
Select Case DataErr
Case DUPKEY
Me.ProjectNumber = Me.ProjectNumber + 1
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
You can still show your PojectID as a single value in a query or report for
instance. In a query enter the following in the 'field' row of a blank
column in the query design grid:
ProjectID:[ProjectYear] & "-" & Format([ProjectNumber],"000")
In a report either base a control on a computed ProjectID column in the
underlying query or use an unbound control with a Controlsource of:
=[ProjectYear] & "-" & Format([ProjectNumber],"000")
If you have a ProjectDate field substitute Year([ProjectDate]) for
[ProjectYear] in the above expressions.
Ken Sheridan
Stafford, England