Solved

Custom AutoNumber in MS Access

Posted on 2009-03-31
8
832 Views
Last Modified: 2013-11-29
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
Comment
Question by:diana_g
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:OdeMonkey
ID: 24029031
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24029068
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24029551
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:diana_g
ID: 24030905
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
 
LVL 77

Expert Comment

by:peter57r
ID: 24031147
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24031326
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
 

Author Closing Comment

by:diana_g
ID: 31564811
I believe I have convinced the team that this solution works well.  Thank you Peter and JeffCoachman for the help.
0
 

Author Comment

by:diana_g
ID: 24041098
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now