Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Custom AutoNumber in MS Access

Posted on 2009-03-31
8
Medium Priority
?
836 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 2000 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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 

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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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