simple database primary key question - help

[Fill-in Form General Questions]

I am very very very new to Access and need some help.

I have a simple database with 3 tables and 1 lookup table that I just
started working on. I would like to make the primary key on the main table
the "ProjectID", the format needs to be XXXX-XXX. I would like for the first
XXXX to be the year and XXX to be the sequential project# starting from 001.
I don't mind hard coding the year if I have to because the table will have at
most 30 records. How do I set this up, what should the data type, input mask,
etc. be???

 

[Fill-in Form General Questions]

I forgot to ask. How can I set it up to automaticall increment the project
number by 1 every time a new record is added.

 

[Ken Sheridan]

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