Autogenerate Tracking Number

[Guest]

Hello,

I need to have a tracking number autogenerate in such a way.

There are three departments:
FIN - Finance Canada
TBS - Treasury Board Secretariat of Canada
PSHRMAC - Public Service Human Resources Agency of Canada

We would like to have a tracking number that would equal a dated stamp from
the paper. The format would be the following.

Department Letter + Financial Year = Sequential Number
Ie. F-07-0000001 then F-07-0000002 etc for FIN doc entered in FY07
T-07-0000001 then T-07-0000002 etc for TBS doc entered in FY07
P-07-0000001 then P-07-00000002 etc for PSHRMAC doc entered in FY07

As explained above we would have 3 stamps, one for FIN, one for TBS, one for
PSHRMAC for each financial year coming and would all have sequential
numbering to them and these woudl be stamped on the paper copies of the
documents. When these documents are logged in the tracking system that I am
building, as they are entered, the tracking number shoudl matched the stamped
number. For this I would have the user pre-select the deparment for which
they are entering info for. If possible I would like to have the F-07
(based on "FIN" being selected, pre-populated in the field, and the autogen
feature do the 1, 2, 3, 4........101 etc.

How hard would it be to do this, and is it even possible?

Would require a response to this ASAP.

Thank you very much for you response,

Sincerely,


Is this doable using one table?

I would have a form where

 

[Guest]

Hi Jean-Francois,

There would be no need to store the entire tracking number, as I'm sure you
would already be storing the Department and the year... You would need to
have a new field in your table called SeqentialNumber or similar, and what
you would do is this: when a new record is created (after the user has
selected the department and year) simply do a dmax similar to this:

dmax("SequentialNumber", "TABLENAME", "DeparmentID = " & me.cboDepartment &
" and YearSelected = " & me.YEARFIELD)

Then, you could increment the returned number by 1 and save it as your
Sequential Number. It's simply a matter of using the information you have to
format your Tracking Number as needed, as you will already have the data
required.

I would recommend NOT using the tracking number as the primary key, but
rather have an autonumber primary key as per usual.

If you have many concurrent users you might want to put your code in a loop
to ensure that noone else has recorded the same number while your code was
executing.

Hope this helps.

Damian.

 

[Guest]

Hi Damian,

thanks for your help, this does help, however I am not 100% quite sure on
how to go abouts putting the code in a loop...and how to have the coding be
perfect. Could you clarify a little further on that note?

From what you state, it seems a lot easier then I would have imagined, so
thank you for your help.

Sincerely,

Jean-Francois Gauthier

 

[Guest]

Would this Sequential Number be a copy of the autogen number or another
autogen number? I'm a litlte confused as to why I would still require the
original tracking nubmer to autogen itself, but also have another field
called Sequential Tracking Number that would also autogen....maybe I am
getting this all wrong?

Please excuse me if this is sounding really stupid on my part, I've been
working at this database now fro 11hrs consecutive....

Looking forward to your reply.

JF

 

[Guest]

Hi Jean-Francois,

In the After update event of your combo to select the department or year
(depending on how you are calculating the year) you would use a loop similar
to this:

' get the next sequential number
me.SEQUENTIALNUMBER = dmax("SequentialNumber", "TABLENAME", "DeparmentID = "
& me.cboDepartment &
" and YearSelected = " & me.YEARFIELD) + 1

' force a save
docmd.runcommand accmdsaverecord

' check that the number hasn't been used before
do while not isnull(dlookup("SEQUENTIALNUMBER", "TABLENAME", "DeparmentID =
" & me.cboDepartment & " and YearSelected = " & me.YEARFIELD & " and
SEQUENTIALNUMBER = " & me.SEQUENTIALNUMBER & " and IDFIELD <> " & me.IDFIELD)
' If the number has been used before, in the same year, for the same
department
' and is NOT this record (hence excluding IDFIELD), it will get to here
me.SEQUENTIALNUMBER = me.SEQUENTIALNUMBER + 1

' force a save again
docmd.runcommand accmdsaverecord
loop

Hope this helps. It's a bit messy on the screen here, but should give you
an idea about it...

Damian.

 

[Guest]

Hi again,

I am assuming that you have some sort of autonumbering ID field in your
table, that has no bearing whatsoever on your tracking number fields. You
need to calculate the next available sequential number for the specified
department and year, but you need to ignore the one that belongs to the
current record (if you have already created one).

Forget about the loop for the time being and concentrate on getting the
calculation of the next available number. From there you can work out how/if
you need to use the loop for added protection.

Damian.

 

[Guest]

Hi Damian,

Thanks again for your help on this one. I'm still having trouble
understanding however to get this to work.

Again, this is what I would like happen. On the form there are 3 fields of
importance here. The year field, the department field and then tracking
number field. I want the year and number field to basically decide on what
the tracking number will be.

The tracking number should have a format as such "YY-D-######".
YY = last two digits of the year selected
D = first string letter of the department choice
###### = sequential number but in the format that there are 6 digits
starting at 000001.

For example, if the user chooses 2007 as the year and the department being
Finance, then the tracking number would be 07-F-000001 (being the first
record chosen for that year and for Finance). If the user then would select
the next record as 2007 and TBS as the department then the next sequential
number would be 07-T-000001 (being the first record for TBS iin 2007). This
number would grow in increments of one every time a new record is inputted.

Is this doable. I think that you are on the right track with the DMAX
function, however I am unsure as to how to get this to work, and how each of
these fields in the table should be programmed as weither year should be a
date or text field, Department would obviously be a text, and the last set of
numbers would be an autoNumber or just a number.

Hope you can clarify a little further and get me up and running on this one.

Thank you very much for all your help, it is greatly appreciated.

Sincerely,