Trying the inadvisable…. How to create an incremental reference number that resets each year? :O

[Yesvice]

Hi there

I'm attempting to make a fancy access program to replace our organisations current system of dozens excel spreadsheets and hundreds of word documents and even pen and paper.
Though I am supernew to all access (or coding in general) I am keen to give this whole database building thing a go!

Unfortunately I am stuck at the second requirement (of 20 or so) which is trying to create a process that assigns a reference automatically in a particular format. Specifically I need a reference made of a sequential or incremental number and then the year, e.g. or 6897/2020. Each year should reset to zero so if 6897/2020 was the last item of that year the next entry will be 1/2021.

Now I have used my best googling skills and understand that one should avoid this pathway unless necessary but sadly this reference gets used heavily in our organisation and it isn’t going to change. I also understand I should leave the autonumber ID in each table well alone. Finally I have seen hints that I could cheat and make this reference appear in forms but not in the tables, which I would be totally up for if its true [IMG]file:///C:/Users/Ben/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png[/IMG]


So far I am trying to proceed by doing the following.

1. Create column for Increments, one for year and separate column for reference which combines the two using the default value function.
2. Use 'before change' function (expression builder?) on Access 2019 to check that the current year(date()) is not same as the DMax (or is it just Max?), in the year column and if it does not match set increments to 1, else DMax Increments + 1.

So far I can't get anything to work, I tried using the following but I figured my lack of coding knowledge is part the issue

if DMax[TestYear]<>year(date()) then [Increments] = 1


Else DMax("Incremental") + 1

Any pointers on what I should actually be doing?

[pbaldy]

I would probably only store the increment number, presuming there's a date field that the year can be derived from. Put them together on forms/reports for the user. Your formula is more like:

Nz(DMax("IncrementField", "TableName", "Year(DateField) = " & Year(DateField)), 0) + 1

[Yesvice]

Thanks pbaldy

Brilliant I can use forms to do stuff, I thought they were just a pretty UI!

Very novice question, where do I input the formula? Maybe I should play around with forms, was going to get the tables perfect and learn forms later but I think this was an error on my part

[pbaldy]

I think you're on the right track getting the table design right before moving on. The before update event of the form is probably most common, testing to make sure the field doesn't already have a value (or testing Me.NewRecord).

[Yesvice]

Thanks again, I shall learn the ways of the form!

[pbaldy]

Happy to help and welcome to the site by the way!

[Yesvice]

Ok I started learning forms, and played around for a bit but have not found much tutorials out there regarding form events, particularly in the 2019/2016 version. I have tried plugging the formula into various places in either 'Properties' or 'Form Properties'. In Properties I have tried 'Before Update' and in Form Properties I have also 'Before Update' and 'Before Insert', and sadly none of these causes anything to appear in the increments column. The only thing that sort of worked was putting the code into 'default value'.

[pbaldy]

The code would go here:

http://www.baldyweb.com/FirstVBA.htm

In your case the form's before update event and

If Me.NewRecord Then
  Me.TextboxName = Nz(DMax("IncrementField", "TableName", "Year(DateField) = " & Year(DateField)), 0) + 1                 
End If

Replacing all the names as appropriate.

[Yesvice]

Thanks pbaldy, added it to 'before insert' and works like a charm

I understand most of the code but the part where is says
"Year(DateField) = " & Year(DateField)

Is this what will make it revert to 1?

[pbaldy]

No, that's the criteria that makes sure you're getting the number for the appropriate year. The +1 at the end adds one to the max existing number, the Nz() function in combination with the +1 will make it revert to 1 in a new year.

[Yesvice]

Yay, that's magic!

Ok there is a part 2 to this
In another field I want to put in an automated document name using the rows number and date field

E.g. TEST00000012020.doc

I'm guessing something like Doc = "[TEST]" & Format("[IncrementField]", "0000000") & "[YearField]" & ".doc"

[pbaldy]

Delete the quotes around YearField:

Doc = "[TEST]" & Format("[IncrementField]", "0000000") & [YearField] & ".doc"

[Yesvice]

Hey it all works!

So my code at the end is:

If Me.NewRecord Then
Me.Increment = Nz(DMax("Increment", "DatB", "Year(TodayA) = " & Year(TodayA)), 0) + 1
Me.DocT = "Test" & Format([Increment], "0000000") & [TYearA] & ".doc"

End If

Anywho thanks for the help!

[pbaldy]

Happy to help and welcome to the site by the way!