Annual Serial Number using Macro Solution

[SlingerJM]

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

[zmbd]

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.

[SlingerJM]

Yes, I saw those threads, tried their solutions, esp NeoPa's, did not work for me.

[zmbd]

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.

[NeoPa]

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.

[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:

   If [IsInsert] Then
   SetLocalVar
      Name YearPrefix
      Expression = Format(Date(), "yy")
 
   SetLocalVar
      Name nextSeq
      Expression = 1
 
   Look Up A Record In  
      SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
      Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
      Alias z
 
   SetLocalVar
      Name nextSeq
      Expression = Val(Right([z].[CR Number],4)) +1
 
   SetField
      Name [CR Number]
      Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
   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".

[zmbd]

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.

[SlingerJM]

@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