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".
5
