Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

Custom AutoNumber in MS Access

Hello Experts,

I am creating a new database in which we are storing records for three categories - RM, PK and FG.  We currently track how many we have of each on an Excel spreadsheet where each entry has the following format for unique identification:

2009-01-RM
2009-02-PK
2009-03-FG
2009-04-RM
2009-05-RM
etc..

The unique identifier is a combination of: 1) The current year, 2) A sequential number (i.e. you cannot go from 3 to 5, a 4 must exist), and 3) The category (RM, PK or FG).

Also, when a new year starts, the sequential numbering starts again (i.e. 2010-01-FG).

This formatting I cannot change because it is required for audit purposes.

What is the best way to go about this and how do I do it?  I imagine using the autonumber property is still a good idea to build relationship between tables and maybe have this identifier as another field that is automatically generated.  But, I also know that you can only have one autonumber field per table.  I am not very good at coding, so if this is the case, I would appreciate some help.

Thanks!
0
diana_g
Asked:
diana_g
  • 3
  • 2
  • 2
  • +1
1 Solution
 
OdeMonkeyCommented:
Yes, I would use an autonumber field to maintain links, and create the identifier in code.  You cannot easily use an autonumber field for the sequential portion of the identifier because it is complex to reset the value annually.  I would create a table to hold the last sequence number used for each of the categories (in a given year, if it is possible you want to be able to add records for more than one year concurrently).  This will provide the basis for the next identifier in each category, and will require an update every time a new identifier is created.

Dan
0
 
peter57rCommented:
In my opinion, the minimum code will be required if you store the parts of the number in 3 separate fields.
This will allow you to a simple built-in function to get the next number and you can join the fields together with a simple expression if you ever need to.

The only uncertainty I have is 'when is the current year = the current year?'
To expand on this nonsense, what happens on say 2nd Jan 2009?  Is that always going to be 2009 or could you have some 2008 records still to enter?

If Current year really = current year then you can set the default value of the yearfield to Year(Date()); you can set the value of the central sequence number to :
=Dmax("SeqNo", "tablename", "Yearfield= & year(date()))
And I guess you would use a combo for the final bit.

To present the fields as one value you can use this expression in queries :

=Yearfield & "-" &  format(seqNo,"00") & "-" & Category

0
 
Jeffrey CoachmanCommented:
As you can see, this is not as easy as it looks.
;-)

The other issues with these types of systems is that the date entry is Non-standard.
You must create the new record first (so you can have the category) THEN you can generate the "Key"

The other issue is along the same lines of what Pete stated:
What happens if a Category changes?

If this system inserts this value into the table, then will it need to "update" the Key, if the Category changes?

In any event, here is a sample.

It is a very basic sample based loosely on Pete's post.

Use it as you wish.
;-)

Pete was first to post this method, (I was too slow) so direct the points to him, not me, if you choose to use this.

JeffCoachman
Accesss-EEQ24280983-CustomAutoNu.mdb
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
diana_gAuthor Commented:
Experts, thank you for your comments and the sample =)

As a quick question - How would I reset the sequence number once the new year starts?  

And, yes, there is the possibility that we may have a 2008 entry that needs to be entered now so year may not always equal Current Year.

Also, we may need to go back and change the category (RM to FG, for example) and therefore, the key would change.
0
 
peter57rCommented:
Based on this then, I would say it is quite important that you use a separate autonumber field for the record key to use in relationships with other tables.  I don't like situations where a link field value is liable to change.

If the year is not necessarily the current year then you can still use the default expression but you will have to add code to the afterupdate event procedure of the Year textbox to re-calculate the sequence number if the user changes the year.

Me.txtSeqNo = Dmax("SeqNo", "tablename", "Yearfield= & year(date()))

I think you should lock the year field once the record has been saved, otherwise a user could change it and so create a gap in the numbers.

There is no issue with a new year, because the current max value will be zero and so the next number is 1.

0
 
Jeffrey CoachmanCommented:
If you look at my code, it simply uses the current Year.

So the year will always be the current year.
However, ...the counter portion of the value would continue to count.

As you are seeing (with you further requests), this system requires you to consider *Lots* of parameters.

Again, as Pete and I have stated, systems like this always have these types of issues.
That is what makes them difficult to work with.

Even if you did all of this, a user will still probably find a way to goof it up to the point where you could not fix it.

So first you need to decide if you really want to use a system like this. (with all of its issues)
Let us know.

I am not really keen on this system.
I presented the sample as a starting point for you to build on.

I'll take a whack at resetting the numeric portion of the code only, but the rest will be up to you.
Would that be acceptable?

JeffCoachman
0
 
diana_gAuthor Commented:
I believe I have convinced the team that this solution works well.  Thank you Peter and JeffCoachman for the help.
0
 
diana_gAuthor Commented:
Experts - I've managed to convince the team that there is no need to reset the numeric portion of the identifier as a new year starts.  For the potential trouble it could cause, it is not worth investing the time to code.  I'll be able to use the posted solution.  As always, thank you for the help =)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now