I have 5 consultants in my table called tblConsultant. I want to generate
Task Order numbers for each consultant as a project is awarded to a
particular consultant. I have assigned a two digit consultant code to each
consultant and would like the task order number generated to be an
incremental number that combines the following :
A prefix of "NE", the two digit consultant code, the four digit year (2005)
and then the incremental number (starting at 100). As a new task order is
generated for this consultant (it could be on this project or on a new
project), I would like the project number to increment by one. I think I
need to store this number as a field in my table, rather than just
concatenating other fields on my form. My two questions are: (1) How do I
keep track of the counter for each consultant, and (2) How do I program
Access to automatically generate the task order number each time a new task
order is created?

You need a field in your table to carry the Task Order Number. If this is a
single user system, it is very simple. If it is a multi user system, then
you need to program for the possibility that another user could be adding a
project for the same consultant and one of you could overwrite the other's
entry.

Since you need the consultant's code, this should probably be in the After
Update event of the control where you select the consultant:

strTaskOrder = "NE" & Me!txtConsultant & Cstr(Year(Date())
strNext = (IsNull(DMAX("[TaskOrderNo]","MyTableName", _
&"left([TaskOrderNo],8) = '" & strTaskOder & "'")

If IsNull(strNext) Then ' No Number Found
strTaskOrder = strTaskOrder & "100"
Else
intNextNo = Cint(Right(strNext,3)) + 1
Me.TaskOrder = strTaskOrder & Cstr(intNextNo)
End If

At this point you will have the next Task Order Number. Now, to ensure you
don't have the multi user problem, in the Before Update event of the form:

If Not (IsNull(DLookup("[TaskOrderNo]","MyTableName", _
&"[TaskOrderNo] = '" & Me.TaskOrder & "'") Then
'Do whatever you need to do here to let the user know and assign the next
number
End If

"dshort" wrote:

> I have 5 consultants in my table called tblConsultant. I want to generate
> Task Order numbers for each consultant as a project is awarded to a
> particular consultant. I have assigned a two digit consultant code to each
> consultant and would like the task order number generated to be an
> incremental number that combines the following :
> A prefix of "NE", the two digit consultant code, the four digit year (2005)
> and then the incremental number (starting at 100). As a new task order is
> generated for this consultant (it could be on this project or on a new
> project), I would like the project number to increment by one. I think I
> need to store this number as a field in my table, rather than just
> concatenating other fields on my form. My two questions are: (1) How do I
> keep track of the counter for each consultant, and (2) How do I program
> Access to automatically generate the task order number each time a new task
> order is created?