Solved

Custom AutoNumber in MS Access

Posted on 2009-03-31
8
833 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

756 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