Customize auto-number field

[New2Access]

I'm creating a db in Access 2007 from data that's currently being tracked on
a spreadsheet. What the db will do is allow a hand full of users to enter
specific case information using a form. . . a tracking number, the Module
affected (listed in a drop down), the subject, a description, the date the
case was submitted, the severity of the case (1 being the lowest & 4 being
the highest), the case reason (listed in a drop down), the user submitting
the case, and any info on the resolution of the case.

My problem is that the users have requested the tracking number to be in the
following format: Module abbreviation + 4-digit case number (incremental)+
2-digit year. An example for the first case submitted this year on System
Performance would be SP000109. Each module would have a different set of
case numbers and they would need to start over at the beginning of the year.

Right now, the main table, tblCaseReport, is set up with the case id being
an auto-number field and I'm wondering if it's possible to customize it as
explained above.

All suggestions/ideas are greatly appreciated.

 

[Mr. B]

New2Access,

To answer your direct question quickly, you cannot modify the autonumber
field. It is, as you stated, in incrementing number, and is never repeated.
Is is only a numeric field. I would reccomment keeping the autonumber field
as a record ID.

Now to address the indirect things you want to know: You can add another
field to your table and have it be just a long integer number type field and
then programmatically increment that number for each new record and use this
number in the custom tracking number. You might also need a "tracking number
field" as a text type field to store the complete tracking number.

To create Each new "tracking number" you would use the selected "Module" to
acquire the appropriate prefix (you indicate that your user is seleting this
value).
You would get the maximum number from the new field that you are
incrementing and increment that number by 1 if the current year is equal to
the max year from your "tracking number" field, but if the current year is
greater than the max year then you would start the numbering over at "0001".
You would also just append the last two digits of the current year as the
suffix for your new "tracking number".

Each time a new record is added you will need to read the existing data and
take action accrodingly:

Get the max of the last two chracters of the "tracking number" field.
Get the max of the incrementing field.
Determine the appropriate prefix for the "tracking number" from the section
made by the user from the "Module Affected" field.

You would then concatenate these values together to create the new "tracking
number" and apply that number to the "tracking number" field.

 

[New2Access]

Thanks, Mr. B., for such a quick response. That's exactly what I need to do.
I thought I had gotten it to work until I realized the Case id (incrementing
number) wasn't by Module. Each time a new case is added, the case id is
incrementing regardless of the module selected.

Example - A new case entered for module 'TB' should have a tracking number
of 'TB000109'. Instead it's tracking number is 'TB000409' (there are 3 other
records in the db right now.

I feel like there's something small yet obvious that I'm overlooking but I
can figure it out. I'm stuck.

Thanks again for your help.

 

[Mr. B]

I should have caught this. Sorry. You will not actually need the field to
hold the 4 digit numbers. You will have to get the maximun value of these 4
digits from the CaseId field.

You will need to use an sql statement to retrieve only the Case ID values
that start with the prefix and end in the current year. So, just create a
query that returns all of the records that match these two criteria and
create a field for the 4 digits of the actual number using the Mid function
like:

Number: val(mid(YourFieldName,3,4))

then set your sql statement to return only the max of this new field.

Hope this makes sense.

 

[New2Access]

Thank you so much, Mr. B. That did it!

Please accept my apologies for the delayed response.

 

[Mr B]

No problem with the resonse time. I am just glad that we were able to
resolve your issue.

Mr B
askdoctoraccess dot com