By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Annual Serial Number using Macro Solution

P: 5
Real new to Access 2010. Condition Report system identifies new entries as YY-####, ex, 15-0031. I need a field "CR Number" to automatically produce this format for a new record. This then becomes the main (controlling) field for all forms and reports. I started with "CR Number" as an autonumber with a prefix in 'format' "15-"0000. However, when query for forms, would not accept prefix. Condition Report 15-0009 would be queried only if the user entered "9". The user will be entering the whole CR Number: 15-0009. So I need a field which automatically generates a new number in the format yy-#### that I can query. Please be as specific as you can with answer. Where to type information etc. I have read some solutions for others and tried them but not very helpful. I have tried typing in "Field Properties", Macros, Expressions, etc. Nothing works. I need to know what to type, and where to type it. Please help if can.

Thanks,

Joe
Apr 21 '15 #1

✓ answered by SlingerJM

I spent three days searching the internet for a solution to my problem. I had some help from our IT expert and I now have a solution. First, let me repeat what I needed. I needed a Condition Report(CR) Number which will drive my database. I created a field "CR Number". I needed information in that field to be prefixed with the current year, by last two digits, a hyphen, and a sequencing number that reverts back to 0001 at the beginning of each year. For example, I needed a number like: 15-0032 and the first CR of next year would be 16-0001.
There are solutions shown on this site, but not a lot of directions for someone like me with little to no coding or Access experience so I am going to detail not only the solution, but "how to" employ it in MS Access 2010.

I created a table with a field "CR Number". In the "Datasheet View", I clicked on the field/column for "CR Number". I then went to the ribbon under "Table Tools" and clicked on the ribbon "Table". Icons appeared on that ribbon and I clicked on the icon "Before Change". This is MS Access' Macro developer. Then I worked through the helping screens to insert the following code:

Expand|Select|Wrap|Line Numbers
  1.    If [IsInsert] Then
  2.    SetLocalVar
  3.       Name YearPrefix
  4.       Expression = Format(Date(), "yy")
  5.  
  6.    SetLocalVar
  7.       Name nextSeq
  8.       Expression = 1
  9.  
  10.    Look Up A Record In  
  11.       SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
  12.       Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
  13.       Alias z
  14.  
  15.    SetLocalVar
  16.       Name nextSeq
  17.       Expression = Val(Right([z].[CR Number],4)) +1
  18.  
  19.    SetField
  20.       Name [CR Number]
  21.       Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
  22.    End If
When the screens pop up and you fill in the lines, some of what you are typing (IsInsert, SetLocalVar, Look Up A Record In, SetField, etc.) automatically populates or appears in drop down menus.

I hope this helps anyone else in this situation.

I forgot to mention that the "CR Number" Field in my table was set as Data Type: Text

One last thing,

The name of my Table is "CR Database".

Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,387
There are a few other threads covering similar needs to your request. Please use the search feature :)
Key words:
Serial numbers, sequence numbers, year

To get you started here two: http://bytes.com/topic/access/answer...increments-one
NeoPa's suggestion in Post#6 is a very good one to use.

Here's another thread that should point you in the right direction:
http://bytes.com/topic/access/answer...-serial-number

Also we will need to see what code you've already tried.
Apr 21 '15 #2

P: 5
Yes, I saw those threads, tried their solutions, esp NeoPa's, did not work for me.
Apr 21 '15 #3

zmbd
Expert Mod 5K+
P: 5,387
Would you please post your SQL/VBA that you attempted along with an example/explanation of what results you were obtaining?

Please format the code/script using the [CODE/] button in the toolbar.
Apr 21 '15 #4

NeoPa
Expert Mod 15k+
P: 31,347
Hey Joe. Why don't you tell us exactly what you used as an attempt to follow the instructions I posted, and also what happened (as not working can describe hundreds of different results). This gives us some information from which to work to deduce what might be going wrong in your case. We know it works generally. What we have no idea about is in what ways your attempts are different from the working ones.
Apr 21 '15 #5

P: 5
I spent three days searching the internet for a solution to my problem. I had some help from our IT expert and I now have a solution. First, let me repeat what I needed. I needed a Condition Report(CR) Number which will drive my database. I created a field "CR Number". I needed information in that field to be prefixed with the current year, by last two digits, a hyphen, and a sequencing number that reverts back to 0001 at the beginning of each year. For example, I needed a number like: 15-0032 and the first CR of next year would be 16-0001.
There are solutions shown on this site, but not a lot of directions for someone like me with little to no coding or Access experience so I am going to detail not only the solution, but "how to" employ it in MS Access 2010.

I created a table with a field "CR Number". In the "Datasheet View", I clicked on the field/column for "CR Number". I then went to the ribbon under "Table Tools" and clicked on the ribbon "Table". Icons appeared on that ribbon and I clicked on the icon "Before Change". This is MS Access' Macro developer. Then I worked through the helping screens to insert the following code:

Expand|Select|Wrap|Line Numbers
  1.    If [IsInsert] Then
  2.    SetLocalVar
  3.       Name YearPrefix
  4.       Expression = Format(Date(), "yy")
  5.  
  6.    SetLocalVar
  7.       Name nextSeq
  8.       Expression = 1
  9.  
  10.    Look Up A Record In  
  11.       SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
  12.       Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
  13.       Alias z
  14.  
  15.    SetLocalVar
  16.       Name nextSeq
  17.       Expression = Val(Right([z].[CR Number],4)) +1
  18.  
  19.    SetField
  20.       Name [CR Number]
  21.       Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
  22.    End If
When the screens pop up and you fill in the lines, some of what you are typing (IsInsert, SetLocalVar, Look Up A Record In, SetField, etc.) automatically populates or appears in drop down menus.

I hope this helps anyone else in this situation.

I forgot to mention that the "CR Number" Field in my table was set as Data Type: Text

One last thing,

The name of my Table is "CR Database".
Apr 22 '15 #6

zmbd
Expert Mod 5K+
P: 5,387
Very nice to have a macro version of this.
Also an interesting use of the table-level action macros.
We more than likely would have pointed you towards a VBA or SQL version.

Note that I used the [CODE/] format around your posted script.. it is required for all posted script... and has the benefit that tables of text and the indenting is retained.
Apr 22 '15 #7

P: 5
@zmbd
From SlingerJM:
I saw the [CODE/], tried it but could not figure it out. So simply did it the old fashion way. I am not a code writer so I doubt I will be submitting anything again; however, this one perplexed me and as much searching has I have done, I saw there were many others in same situation. So since I had a solution that worked, I thought I would share it in the only way I knew how. Thanks for your help on making it correct for the forum
Apr 22 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.