Solved

Custom AutoNumber in MS Access

Posted on 2009-03-31
8
828 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

21 Experts available now in Live!

Get 1:1 Help Now